Forum Discussion

qmartiny's avatar
qmartiny
Icon for Helper III rankHelper 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
Icon for Helper III rankHelper 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
Icon for Super User rankSuper 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))