Forum Discussion
Clustered Bar Chart with Unpivoted Data - Combining Two Types of Data
Keep the unpivoted table (you can get rid of the other one) and create a relationship from Agency to LeadAgency and another (inactive) relationship from Agency to InvolvedAgency.
The CountLead measure stays the same but the SumInvolved has to activate the inactive relationship:
SumInvolved = VAR CurrAgency = VALUES ( dimAgency[Agency] ) RETURN CALCULATE ( SUM ( Unpivoted[Value] ), Unpivoted[Involved Agency] IN CurrAgency, USERELATIONSHIP ( Unpivoted[Involved Agency], DimAgency[Agency] ) )
Keep the unpivoted table (you can get rid of the other one) and create a relationship from Agency to LeadAgency and another (inactive) relationship from Agency to InvolvedAgency.
The CountLead measure stays the same but the SumInvolved has to activate the inactive relationship:
SumInvolved =
VAR CurrAgency = VALUES ( dimAgency[Agency] )
RETURN
CALCULATE (
SUM ( Unpivoted[Value] ),
Unpivoted[Involved Agency] IN CurrAgency,
USERELATIONSHIP ( Unpivoted[Involved Agency], DimAgency[Agency] )
)
AlexisOlson That did the trick! Thank you!
Now can i get your advice for how to learn Dax like you? 🙂 I still don't really understand how your calculate function works.
CALCULATE is a tool to modify the context a calculation is performed in. Typically, it modifies the filter context via column and table filter arguments but there are a few additional functions that work in conjunction with it: USERELATIONSHIP, CROSSFILTER, REMOVEFILTERS, and KEEPFILTERS.
It's a pretty complicated function and the SQLBI guys devote a whole chapter to CALCULATE in their book The Definitive Guide to DAX. They have a shorter version here:
https://www.sqlbi.com/blog/marco/2010/01/03/how-calculate-works-in-dax/