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: Name Group John Group 1 John Group 2 Matthew Group 1 Mary Group 1 Mary Group 2 Susan Group 1 Lisa Group 2 Samuel Gr...
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

     

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!