Forum Discussion

flyinggnugget's avatar
flyinggnugget
Frequent Visitor
2 years ago
Solved

Using measures to find overlapping between 2 or more categories

Hi All,

 

Suppose I have the following data:

NameGroup
JohnGroup 1
JohnGroup 2
MatthewGroup 1
MaryGroup 1
MaryGroup 2
SusanGroup 1
LisaGroup 2
SamuelGroup 1

 

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

GroupDistinct Count
Group 15
Group 23

 

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:

GroupDistinct Count
Group 13
Group 21
Both2

 

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!

Need Help
  • mh2587's avatar
    mh2587
    2 years ago
    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's avatar
    Troekoe
    2 years ago

    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

     

3 Replies

  • mh2587's avatar
    mh2587
    Icon for Super User rankSuper User
    2 years ago
    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's avatar
      flyinggnugget
      Frequent Visitor
      2 years ago

      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!

  • Troekoe's avatar
    Troekoe
    Frequent Visitor
    2 years ago

    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