CODEWITHSUNDEEP
powerbidax
Student of the Digital World
Student of the Digital World

Reputation: 3041

PowerBI Desktop - Identifying Common Items that belongs to the Selected Values in a column

I am trying to do some analytics on PowerBI. I am struck at this logic, where I am trying to Identify the common items with in the group and not common items with in the group.

For Example,

Group Name  Contact
GroupA      Rock 
GroupA      Eddy
GroupA      Brown
GroupB      Rock 
GroupB      Katie
GroupC      Eddy
GroupC      Brown
GroupC      Katie

If I select GroupA and GroupB then I want to output the Common Items as 1 and not common Items as 3.

The Common Item is Rock The Not Common Items are Eddy, Brown & Katie.( I am looking to get this as part of my output visual as well)

How can we do this in PowerBI which works dynamically when we select multiple groups (more than 2 as well)?

Upvotes: 1

Views: 46

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

I would approach this by checking if the number of groups the Contact appears in is the same as the number of groups that you have selected:

Common = IF(COUNTROWS(VALUES(Groups[Group Name])) =
               COUNTROWS(CALCULATETABLE(VALUES(Groups[Group Name]),
                                     ALLSELECTED(Groups))),
               "Common", "Not Common")

Then you can use these labels inside a second measure to get the count:

Intersect = IF([Common] = "Common",
               CALCULATE(DISTINCTCOUNT(Groups[Contact]), 
                         FILTER(ALLSELECTED(Groups[Contact]), [Common] = "Common")),
               CALCULATE(DISTINCTCOUNT(Groups[Contact]),
                         FILTER(ALLSELECTED(Groups[Contact]), [Common] = "Not Common")))

You can then set up a table or matrix using Contact and the new measure Intersect along with a slicer on Group Name.

Contact Table

Upvotes: 1

Related Questions