Comparative Analysis implementation in Power BI

[linoybar]

Hi,

I'm trying to create a measure that will use an inactive relationship in order to count number of incidents from MyFactTable that will be influenced by user selecting specific values from units filter which is a column in that inactive table named UnitsTable1.

What I have now is that it works only for totals but when I make a selection in a filter I get blank.

The active table is called UnitsTable.

Here is my dax:

Measure = CALCULATE(counta(MyFactTable[IncidentKey],

                   USERRELATIONSHIP(MyFactTable[IncidentKey], MyMappingTable[IncidentKey],

                   USERRELATIONSHIP(MyMappingTable[UnitKey], UnitsTable1[UnitKey])

)

At the end the purpose is to show in a single chart comparison between number of incidents of UnitA vs number of incidents of UnitB+UnitC, for example.

So I've uploaded the UnitsTable twice into my Tabular model. They have UnitColumn which I put as filters. 

So then the user will choose UnitA from UnitColumn from UnitsTable, and Unit B and Unit C from UnitColumn from UnitsTable1, 

and then the chart needs to show 2 lines - one for number of incidents for UnitA, and second for number of incidents for UnitB+C.

Please help 🙂

Thanx!

P.S. To clarify my initial issue, here is the best explanation of what I'm trying to achieve:
https://community.powerbi.com/t5/Desktop/Alternate-states-functionality/td-p/238984

[v-shex-msft]

HI @linoybar,

AFAIK, for calculate function, it use 'and' logic to link each filters.

So if you put multiple filters in it, it will try to find out records who achieve each conditions at same time. 

I think this is the reason of blank calculation result.

Maybe you can try to split your formula as two calculation and summary them get total value:

Measure =
CALCULATE (
    COUNTA ( MyFactTable[IncidentKey] ),
    USERELATIONSHIP ( MyFactTable[IncidentKey], MyMappingTable[IncidentKey] )
)
    + CALCULATE (
        COUNTA ( MyFactTable[IncidentKey] ),
        USERELATIONSHIP ( MyFactTable[IncidentKey], UnitsTable1[UnitKey] )
    )

Reference link:

Order of Evaluation in CALCULATE Parameters

Regards,

Xiaoxin Sheng

[linoybar]

Hi @v-shex-msft,

Thanks for your reply.

In my model MyFactTable is connected to MyMappingTable, and MyMappingTable is connected to UnitsTable1.

As you can see, there is no straight connection between MyFactTable  and MyMappingTable.

Only the connection between MyMappingTable and Unitstable1 is inactive - this is the one I'm trying to activate using USERRELATIONSHIP.

So maybe the part of

USERELATIONSHIP ( MyFactTable[IncidentKey], MyMappingTable[IncidentKey]

is unnecessary since it's always activated anyway.

I tried either ways, but nothing worked...

[v-shex-msft]

HI @linoybar,

Nope, I think it is impossible.

USERELATIONSHIP seems only works on two table who contains direct relationship, I don't think you can nested multiple functions to enable relationship for other tables.

Marco also mentioned for USERELATIONSHIP function , you can take a look at below blog to know more about it.

USERELATIONSHIP in Calculated Columns

Regards,

Xiaoxin Sheng