Forum Discussion
Filter Context / Transition Help
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).
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
4 Replies
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.
Was unaware of this option but believe I have done so now, thanks.
