Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I've been using Power BI for a while and as a non technical user have more recently started studying DAX and context transition in greater detail. I feel I am making progress but am stumped by the following
I have a measure [NoOfAccounts] in a dimension table (Accounts) to distinctcount the dimension linked to my fact tables (Expense)....
[NoofAccounts]=DISTINCTCOUNT([Exp Code])
Whilst experimenting I set up the following measure to try and count only facts used in the Expense Table....
[NoofUsedAccounts] = CALCULATE([NoofAccounts],Expense)
I am referencing the expense table in the filter parameter of caclulate and yet I seem to get the right answer, and am struggling to understand where the transition is occuring here. If I swap to reference a column rather than the table then the answer is wrong so I am confused and would love to understand why the table reference in the filter clause worked.
(I have written a better more udnerstansable measure but would still love to understand why this poorly structured code seemed to work - Thanks very much in advance).
Solved! Go to Solution.
Hi @Badger43
The measure you have written is one version of a related distinct count measure.
See here for a similar example:
https://www.daxpatterns.com/related-distinct-count-excel-2013/
The important concept here is "expanded tables". Providing Expense as a filter argument actually adds the expanded Expense table as a filter, which includes columns of all tables on the 1-side of a relationship with Expense, so includes all columns of Accounts.
The result is that rows of Accounts related to the visible rows of Expense become filters, and [NoofAccounts] gives the expected result.
This is a good article on expanded tables:
https://www.sqlbi.com/articles/expanded-tables-in-dax/
Regards,
Owen
Thanks very much Owen, really helpful. Working my way through 'Definitive Guide to Dax' and haven't had a good look into expanded tables just yet, will make sure to do so.
Hi @Badger43 ,
Could you please mark your post as Answered if @OwenAuger has answered your questions? This will help the others in the community to find this solution easily if they encounter a similar problem to yours. Thank you.
Best Regards
Was unaware of this option but believe I have done so now, thanks.
Hi @Badger43
The measure you have written is one version of a related distinct count measure.
See here for a similar example:
https://www.daxpatterns.com/related-distinct-count-excel-2013/
The important concept here is "expanded tables". Providing Expense as a filter argument actually adds the expanded Expense table as a filter, which includes columns of all tables on the 1-side of a relationship with Expense, so includes all columns of Accounts.
The result is that rows of Accounts related to the visible rows of Expense become filters, and [NoofAccounts] gives the expected result.
This is a good article on expanded tables:
https://www.sqlbi.com/articles/expanded-tables-in-dax/
Regards,
Owen
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |