Forum Discussion

Badger43's avatar
Badger43
Frequent Visitor
4 years ago
Solved

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 f...
  • OwenAuger's avatar
    OwenAuger
    4 years ago

    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

Badger43's avatar
Badger43
Frequent Visitor
4 years ago

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's avatar
    Anonymous
    Not applicable
    4 years ago

    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

    • Badger43's avatar
      Badger43
      Frequent Visitor
      4 years ago

      Was unaware of this option but believe I have done so now, thanks.