Forum Discussion
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 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
10 Replies
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.
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
Yes, that was what I suggested. Just be aware that at the end the measure needs to return a scalar value.
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
