Reply
AlB Super Contributor
Super Contributor
Posts: 1,166
Registered: ‎11-12-2018
Accepted Solution

DAX: doubt with expanded tables

Hi there,

We have an Answers table and a Customers table related through the CustomerKey column. Customers is on the one-side of the relationship and Answers on the many-side. We have the following:

CALCULATE (
      DISTINCTCOUNT ( Customers[CustomerKey] ),
      Answers,
      Answers[AnswerKey] = 6
)


I was expecting the filtering on Answers[AnswerKey] would be visible in Customers, filtering the customers that appear in Answers with Answers[AnswerKey]=6. My reasoning is: we have Answers as expanded table in the filter arguments and further the filter on Answers[AnswerKey]. Answers gets filtered by Answers[AnswerKey] and then, as "filtered expanded table", has an effect on Customers. It does not have any effect from what I see in the results. Would you care to explain why this is so?

Many thanks


Accepted Solutions
Super User
Posts: 751
Registered: ‎09-16-2018

Re: DAX: doubt with expanded tables

[ Edited ]

@AlB

 

The filter you're placing on Answer[Key] = 6 does not propagate to the Customers table. Therefore the only filter  propagating to the customer table is the answer table as it is (without Key = 6 ). 

CALCULATETABLE will create a table filtered by Key = 6 which is then passed to CALCULATE as a filtered argument

 

The expanded version of Customers does not contain coulmns from the Answer table, unless bi-directional filter is activated, which i dont recommend



Did I answer your question? Mark my post as a solution!


Proud to be a Datanaut!

View solution in original post


All Replies
Highlighted
Established Member
Posts: 201
Registered: ‎07-12-2017

Re: DAX: doubt with expanded tables

Hi @AlB

 

Sharing a sample of your base tables and expected result helps the community to answer. If possible please do share.

 

Regards,

Affan

Super User
Posts: 751
Registered: ‎09-16-2018

Re: DAX: doubt with expanded tables

Hi @AlB

 

The right measure in your case should be:

 

CALCULATE (
      DISTINCTCOUNT ( Customers[CustomerKey] ),
      CALCULATETABLE( Answers, Answers[AnswerKey] = 6 )
)

 



Did I answer your question? Mark my post as a solution!


Proud to be a Datanaut!

AlB Super Contributor
Super Contributor
Posts: 1,166
Registered: ‎11-12-2018

Re: DAX: doubt with expanded tables

[ Edited ]

Thanks @LivioLanzo

Why does the first example not produce the same results as yours?

 

Thanks

Super User
Posts: 751
Registered: ‎09-16-2018

Re: DAX: doubt with expanded tables

[ Edited ]

@AlB

 

The filter you're placing on Answer[Key] = 6 does not propagate to the Customers table. Therefore the only filter  propagating to the customer table is the answer table as it is (without Key = 6 ). 

CALCULATETABLE will create a table filtered by Key = 6 which is then passed to CALCULATE as a filtered argument

 

The expanded version of Customers does not contain coulmns from the Answer table, unless bi-directional filter is activated, which i dont recommend



Did I answer your question? Mark my post as a solution!


Proud to be a Datanaut!