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] ) )
You're right. It isn't that simple. The problem is that whichever agency column you for the axis will make things difficult to calculate for the other one.
I'd recommend creating a dimension table with one row for each agency that appears in either column and then using that table's column for the x-axis. Then you can define both measures using the dimension table for filter context.
For example:
CountLead =
VAR CurrAgency = VALUES ( dimAgency[Agency] )
RETURN
CALCULATE (
DISTINCTCOUNT ( Unpivoted[ID] ),
Unpivoted[Lead Agency] IN CurrAgency
)
SumInvolved =
VAR CurrAgency = VALUES ( dimAgency[Agency] )
RETURN
CALCULATE (
SUM ( Unpivoted[Value] ),
Unpivoted[Involved Agency] IN CurrAgency
)
@AlexisOlson. Thanks for your help on this. That solution did work in my simple example where I had a dim table that wasn't linked to the rest of my data, but now I need to make it more complicated. I'm actually already using a dim table with my agencies, and I've linked that dim table to my "LeadAgencies" Table by the Lead Agency. When I do that, the formula for SumInvolved doesn't seem to work. Any more pointers?
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/