Forum Discussion

linoybar's avatar
linoybar
Frequent Visitor
8 years ago

Comparative Analysis implementation in Power BI

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

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      linoybar
      Frequent Visitor
      8 years ago

      Hi Anonymous,

       

      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...

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        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