cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

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

1 ACCEPTED SOLUTION

Accepted Solutions
LivioLanzo Super Contributor
Super Contributor

Re: DAX: doubt with expanded tables

@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 correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

4 REPLIES 4
Highlighted
affan Established Member
Established Member

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

LivioLanzo Super Contributor
Super Contributor

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 correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Super User
Super User

Re: DAX: doubt with expanded tables

Thanks @LivioLanzo

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

 

Thanks

LivioLanzo Super Contributor
Super Contributor

Re: DAX: doubt with expanded tables

@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 correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 165 members 1,734 guests
Please welcome our newest community members: