Forum Discussion
Calculating overlap between calculated columns
Yes, that was what I suggested. Just be aware that at the end the measure needs to return a scalar value.
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
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))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
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))