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

[Mann]

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.

[parry2k]

@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] )

[Mann]

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

[v-lid-msft · Accepted 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]

Thanks @v-lid-msft 

It worked as expected. 😊