cancel
Showing results for 
Search instead for 
Did you mean: 
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
Twitter
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.

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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
Twitter
LinkedIn

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors