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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AlB
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

@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!  

View solution in original post

4 REPLIES 4
LivioLanzo
Solution Sage
Solution Sage

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!  

Thanks @LivioLanzo

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

 

Thanks

@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!  

affan
Solution Sage
Solution Sage

Hi @AlB

 

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

 

Regards,

Affan

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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