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