Using measures to find overlapping between 2 or more categories

[flyinggnugget]

Hi All,

Suppose I have the following data:

Name	Group
John	Group 1
John	Group 2
Matthew	Group 1
Mary	Group 1
Mary	Group 2
Susan	Group 1
Lisa	Group 2
Samuel	Group 1

Doing a simple distinct count for each group would give me the following results:

Group	Distinct Count
Group 1	5
Group 2	3

However, I am trying to find the count of names that are in both group 1 and group 2, in this case I should get "2". How can create a measure to get this value?

To add on, suppose I want to get the following results:

Group	Distinct Count
Group 1	3
Group 2	1
Both	2

Is there a way I can achieve this result using purely measures? I am using a live connection to a semantic model so I won't be able to create calculated columns and tables.

Thanks!

[mh2587 · Accepted solution]

Distinct Count Group = //Try this and adjust your Table Names and Column Names
VAR Group1Names = CALCULATETABLE(VALUES('Table'[Name]), 'Table'[Group] = "Group 1")
VAR Group2Names = CALCULATETABLE(VALUES('Table'[Name]), 'Table'[Group] = "Group 2")
VAR CountBothGroups = COUNTROWS(INTERSECT(Group1Names, Group2Names))
VAR CountGroup1 = DISTINCTCOUNTX(FILTER('Table', 'Table'[Group] = "Group 1"), 'Table'[Name])
VAR CountGroup2 = DISTINCTCOUNTX(FILTER('Table', 'Table'[Group] = "Group 2"), 'Table'[Name])
RETURN
IF(
    HASONEVALUE('Table'[Group]),
    SWITCH(
        VALUES('Table'[Group]),
        "Group 1", CountGroup1,
        "Group 2", CountGroup2,
        "Both", CountBothGroups
    ),
    BLANK()
)

[Troekoe · Accepted solution]

Yo!

To get the exact data which names show up is quite difficult in DAX.
However to get the count of names from group 1 and 2 we can create a DAX measure to count the rows in a table where the names are similar.
Considering the data you suggested we write the following 3 measures:

DISTINCT_GROUPS = DISTINCTCOUNT(Data[Group])

COUNT_NAMES = COUNT(Data[Name])

COUNT_SAME_NAME =

VAR table_1  = DISTINCT(CALCULATETABLE(SELECTCOLUMNS(Data, Data[Name]), Data[Group] = "Group 1"))

VAR table_2  = DISTINCT(CALCULATETABLE(SELECTCOLUMNS(Data, Data[Name]), Data[Group] = "Group 2"))

VAR innerjoin = NATURALINNERJOIN(table_1, table_2)

RETURN

    COUNTROWS(DISTINCT(innerjoin))

After the result we can put the measures in a Matrix and get the following:

hope this helps.

--Troekoe

 

[mh2587 · Accepted solution]

Distinct Count Group = //Try this and adjust your Table Names and Column Names
VAR Group1Names = CALCULATETABLE(VALUES('Table'[Name]), 'Table'[Group] = "Group 1")
VAR Group2Names = CALCULATETABLE(VALUES('Table'[Name]), 'Table'[Group] = "Group 2")
VAR CountBothGroups = COUNTROWS(INTERSECT(Group1Names, Group2Names))
VAR CountGroup1 = DISTINCTCOUNTX(FILTER('Table', 'Table'[Group] = "Group 1"), 'Table'[Name])
VAR CountGroup2 = DISTINCTCOUNTX(FILTER('Table', 'Table'[Group] = "Group 2"), 'Table'[Name])
RETURN
IF(
    HASONEVALUE('Table'[Group]),
    SWITCH(
        VALUES('Table'[Group]),
        "Group 1", CountGroup1,
        "Group 2", CountGroup2,
        "Both", CountBothGroups
    ),
    BLANK()
)

[flyinggnugget]

Thanks for the reply, the first half of the DAX query will be useful for me, unfortunately I won't be able to make use of the part in the return section as I don't have a group named "Both". I provided the example just in case there was some way to get that category out there, but no worries, I can definitely make use of the first half and use a workaround for my visual. Thanks!