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

  • qmartiny's avatar
    qmartiny
    Helper III
    4 years ago

    Here's where I am at the moment.. Getting a blank result where it should work in theory?

     

    Overlap =

VAR MaxSelect = MAX('SP Select'[Selection])
VAR MinSelect = MIN('SP Select'[Selection])

VAR A =  SELECTCOLUMNS(_MAL,"BP",_MAL[BP_ID],"SP",MaxSelect)
VAR B =  SELECTCOLUMNS(_MAL,"BP",_MAL[BP_ID],"SP",MinSelect)

RETURN 
COUNTROWS(INTERSECT(A,B))

     

    • lbendlin's avatar
      lbendlin
      Super User
      4 years ago

      your first two variables get you a scalar value, not a column. You can still do an intersect but it will only test if these scalar values match.

       

      If you cannot hard code the column names then you need to use measures. But you mentioned that these are calculated columns in the first place.  Maybe explain the setup in more detail.

      • qmartiny's avatar
        qmartiny
        Helper III
        4 years ago

        Hm. I am stuck since my slicer doesnt have the same name as my column header anyway..

         

        I'll try using a UNION table to unpivot the data see if that's easier.

         

        Thanks