Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join 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.

Reply
Badger43
Frequent Visitor

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).

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
LinkedIn

View solution in original post

4 REPLIES 4
Badger43
Frequent Visitor

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.

Anonymous
Not applicable

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.

OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
LinkedIn

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.