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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.