Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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()
)
Did I answer your question? If so, please mark my post as a solution!
Proud to be a Super User!
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:
After the result we can put the measures in a Matrix and get the following:
hope this helps.
--Troekoe
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:
After the result we can put the measures in a Matrix and get the following:
hope this helps.
--Troekoe
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()
)
Did I answer your question? If so, please mark my post as a solution!
Proud to be a Super User!
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!
User | Count |
---|---|
97 | |
87 | |
77 | |
67 | |
63 |
User | Count |
---|---|
111 | |
96 | |
96 | |
67 | |
63 |