cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver III

## DistinctCount of a column with GroupBy from a Temporary DAX Table in a Variable

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.

1 ACCEPTED SOLUTION
Community Support

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
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.

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.
4 REPLIES 4
Super User

@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.

Resolver III

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

Community Support

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
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.

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.
Resolver III

Thanks @v-lid-msft

It worked as expected. 😊

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Top Solution Authors
Top Kudoed Authors