Forum Discussion

seanjmorris's avatar
seanjmorris
Regular Visitor
4 years ago
Solved

Clustered Bar Chart with Unpivoted Data - Combining Two Types of Data

Hello  I have a single table (See "Activities by lead agency") that has information about which agency is the lead and any other agencies that are involved. In order to make the second green graph I...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    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] )
    )
seanjmorris's avatar
seanjmorris
Regular Visitor
4 years ago

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. 

 

AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

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/