Forum Discussion

qmartiny's avatar
qmartiny
Helper III
4 years ago

Calculating overlap between calculated columns

Hello lovely people,   Given this table [Account] with calculated columns [SPx].   Account Account SP1 SP2 SP3 AAA 1 1   BBB   1 1 CCC 1     I have created a slicer c...
Need Help
qmartiny's avatar
qmartiny
Helper III
4 years ago

Hello,

 

Thank you for your reply.

 

It looks like I need to create an INTERESECT for every combination of [SP] I want to compare though.

 

Is it possible to use this like a measure so it shows overlap based on slicer selection of [SP]?

 

Thank you

lbendlin's avatar
lbendlin
Super User
4 years ago

Yes, that was what I suggested. Just be aware that at the end the measure needs to return a scalar value.

  • qmartiny's avatar
    qmartiny
    Helper III
    4 years ago

    I am trying something like this first to isolate the SPx columns.


    "Create temporary single column tables from your selected columns" - I suppose you mean a UNION like below.

     

     UNION(
    SELECTCOLUMNS(_MAL,"BP",_MAL[BP_ID],"SP",_MAL[SP1]),
    SELECTCOLUMNS(_MAL,"BP",_MAL[BP_ID],"SP",_MAL[SP2]),
    SELECTCOLUMNS(_MAL,"BP",_MAL[BP_ID],"SP",_MAL[SP3]))

     

    I understand INTERCEPT takes 2 tables max, and I could use FIRSTNONBLANK / LASTNONBLANK? to feed slicer selection ?

     

    Do you have any pointers ? Thank you

     

     

    • lbendlin's avatar
      lbendlin
      Super User
      4 years ago

      Not UNION but INTERSECT. something like this:  

       

      VAR A =  SELECTCOLUMNS(_MAL,"BP",_MAL[BP_ID],"SP",_MAL[SP1])
VAR B =  SELECTCOLUMNS(_MAL,"BP",_MAL[BP_ID],"SP",_MAL[SP2])
RETURN COUNTROWS(INTERSECT(A,B))

       

      • qmartiny's avatar
        qmartiny
        Helper III
        4 years ago

        Thanks for that.

         

        It seems to work however I have 2 issues :

         

        I built a slicer using COMBINEVALUES and a UNION table so I can filter each column in one slicer.

        Using your method on top I have some issues :

        - When I select via slicer SP1 and SP2 I expect the value to be the same, but it returns 0. Any filtering seems to change the value, which I can understand but filtering exactly on the measure content makes the measure null.

        - How do you approach making this measure dynamic ie work taking selection from the slicer? I know selectedvalue but it would only work for the first selection

         

        Again thank you so much for your help.

         

        I have tried using this method aswell but no luck https://community.powerbi.com/t5/Desktop/Calculating-overlap-between-selected-groups/td-p/128624