Calculating overlap between calculated columns

[qmartiny]

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 containing the SP columns so I can select from them.

However I also want to show the % overlap between SP columns ex SP1 = 100 accounts Sp2 = 70 accounts , what is the overlap between the 2?

At the moment selecting multiple SP just gives me the Total but I also want to show which accounts overlap.

I saw a solution that required all the groups to be within the same column. However since they are calculated columns I can not unpivot in the query.

Thanks for your help

Quentin

[Anonymous]

Hi @qmartiny ，

Could you please provide some raw data from the Account table (before creating the calculated columns SP1,SP2 and SP3)? Could you please also provide the calculation logic for these calculated columns SP1, SP2 and SP3?Later I would like to see if these columns can be created in Power Query, if so, maybe we can also use the UNPIVOT function to achieve the final result you want. Even if not, we can find out if there is an alternative way to achieve what you want...

In addition, please check out the following links using different methods(DAX and Power Query). Hope that they can help you solve the problem...

DAX:

Calculating overlap between selected groups

Power Query:

Power BI: Percent Overlap Between Groups

Best Regards

[lbendlin]

Familiarize yourself with the concept of INTERSECT()

INTERSECT function (DAX) - DAX | Microsoft Docs

Create temporary single column tables from your selected columns, and then use Intersect to compare them.

[qmartiny]

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]

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

[qmartiny]

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]

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]

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]

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]

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]

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