Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Guys,
I created a DAX query which is returning a table like this:
Class | Type | IDs |
A | X | 11 |
A | X | 12 |
B | X | 13 |
B | X | 13 |
C | Y | 15 |
C | Y | 15 |
C | Y | 16 |
D | Y | 11 |
For example created Datatable in DAX for easy understanding:
Test =
VAR Source=
DATATABLE("Class",STRING,"Type",STRING,"IDs",INTEGER,
{{"A","X",11},{"A","X",12},{"B","X",13},{"B","X",13},{"C","Y",15},{"C","Y",15},{"C","Y",16},{"D","Y",11}})
Return
Source
I need to find the Distinct count of columns "IDs" as per grouping of "Class" and "Type" using Variable Table "Source". How can I do this in DAX to give me something like below result:
Class | Type | IDs |
A | X | 2 |
B | X | 1 |
C | Y | 2 |
D | Y | 1 |
Thanks.
Solved! Go to Solution.
Hi @Mann ,
We can create a calculated table using following formula to meet your requirement:
Test 2 =
VAR Source =
DATATABLE (
"Class", STRING,
"Type", STRING,
"IDs", INTEGER,
{
{ "A", "X", 11 },
{ "A", "X", 12 },
{ "B", "X", 13 },
{ "B", "X", 13 },
{ "C", "Y", 15 },
{ "C", "Y", 15 },
{ "C", "Y", 16 },
{ "D", "Y", 11 }
}
)
RETURN
ADDCOLUMNS (
DISTINCT ( SELECTCOLUMNS ( Source, "Class", [Class], "Type", [Type] ) ),
"IDs",
VAR c = [Class]
VAR t = [Type]
RETURN
COUNTROWS ( DISTINCT ( FILTER ( Source, [Type] = t && [Class] = c ) ) )
)
BTW, pbix as attached.
Best regards,
Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Mann add a calculated measure with following expression, change table and column name as per your data model
Distinct ID Count = DISTINCTCOUNT ( 'Table (3)'[IDs] )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi @parry2k
I can't add this as a measure since I need to return the expected table which will be used as a dataset in my paginated report. I have to return this expected output as a result of same code.
I tried SUMMARIZE or SUMMARIZECOLUMNS or GROUPBY but none of these helping out.
Mann
Hi @Mann ,
We can create a calculated table using following formula to meet your requirement:
Test 2 =
VAR Source =
DATATABLE (
"Class", STRING,
"Type", STRING,
"IDs", INTEGER,
{
{ "A", "X", 11 },
{ "A", "X", 12 },
{ "B", "X", 13 },
{ "B", "X", 13 },
{ "C", "Y", 15 },
{ "C", "Y", 15 },
{ "C", "Y", 16 },
{ "D", "Y", 11 }
}
)
RETURN
ADDCOLUMNS (
DISTINCT ( SELECTCOLUMNS ( Source, "Class", [Class], "Type", [Type] ) ),
"IDs",
VAR c = [Class]
VAR t = [Type]
RETURN
COUNTROWS ( DISTINCT ( FILTER ( Source, [Type] = t && [Class] = c ) ) )
)
BTW, pbix as attached.
Best regards,
Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |