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

[seanjmorris]

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 unpivoted the agency columns. Both of the tables are in the data model and they're linked via the ID column. My question is how can I combine these two graphs so that, the PQR agency - for example - would have a green bar chart showing that it is involved in 8 projects and a blue bar indicating that it's leading 4 projects. 

[AlexisOlson]

You should be able to use the unpivoted version for both. Distinct count of Local Agency for the blue bars and sum of Value for the green bars.

[seanjmorris]

@AlexisOlson . That doesn't seem to have worked. When I throw in the distinct count of Lead Agency from the Unpivoted data, it's showing me that PQR is in 3 times and DEF is in 4. PQR should be in 4 times and DEF should be in twice. Any other tips?  

[AlexisOlson]

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
    )

[seanjmorris]

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

[AlexisOlson · Accepted solution]

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]

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

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/