Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
9 | |
8 | |
8 | |
8 |
User | Count |
---|---|
13 | |
12 | |
11 | |
10 | |
8 |