DAX aggregate functions from multiple tables

Question

DAX formula for aggregates from two tables and grouping by category.

I have two tables (bugs and properties where each bugs.bug_isue represents turnover in a distinct unit in table properties. I want to query how many bug issues there are in each project as a percentage of the count of units in each project.

      BUGS
--------------------
project | bug_issue
--------------------
north   | blaha
north   | blahb
south   | blahc
west    | blahd
east    | blahe


  PROPERTIES
---------------
project | unit
---------------
north   | n1
north   | n2
north   | n3
west    | w1
west    | w1
south   | s1
south   | s2
east    | e1

What I want to query in DAX is the count of Here's the SQL version of what I want in DAX:

SELECT p.project, 
       (COUNT(b.bug_issue)/COUNT(p.unit)*100) AS percent_turnover
FROM properties AS p 
LEFT JOIN bugs b ON (project) 
GROUP BY p.project;

The following DAX query was unsuccessful. Any advice is greatly appreciated.

Measure = (COUNTA(Bugs[bug_issue)/COUNTA(Properties[unit]))*100

Thanks

RADO RADO · Accepted Answer · 2019-04-24T04:12:26

You need to build a third table, "Projects", that contains a list of unique projects:

"Projects"
project 
---------
north   
west
south
east

Then connect this table to the other 2 tables using field "project", and write 3 measures:

Measure 1:

Bug Count = COUNTA(Bugs[bug_issue])

Measure 2:

Unit Count = COUNTA(Properties[unit])

Measure 3:

Bugs per Unit = DIVIDE( [Bug Count], [Unit Count])

Drop projects from the new table on the rows in a matrix, and add the third measure. You should get the desired result.

How it works:

Power BI is designed to query star schemas. In a star schema, you should always connect 2 fact tables (in your case, "Bugs" and "Properties") via a shared dimension table ("Project"). Such structure allows you to query data from both tables easily.

DAX aggregate functions from multiple tables

1 Answers