Countdistinct of multiple columns and what if parameter

[o59393]

Hi all

I am trying to do a countdistinct dax to multiple columns.

The logic is: count me the unique values for the columns Code and the column Attribute for the value attribute 1.

The dax I used is:

count rows distinct = COUNTROWS(FILTER(GROUPBY(Test111, Test111[Code]), Test111[Attribute] = "Attribute 1"))

I must have a syntax problem because is not recognizing me the function. Can you tell me what could be wrong? The answer for country Costa Rica and year 2019 is 60. (60 for attribute 1 and 2 for atrribute 2). 

The other thing I am trying to do is a count distinct of codes and for attribute 1, were the column values can be dependant on a parameter scroller and have it as flexible as possible.

For example. 

The dax should count me the unique column of codes for attribute 1 were the values are equal to 0 selected on the parameter.

In that case the answer would be 9.

Pretty much the column value should be equal to whatever the you pick up on the parameter scroller.

I attach the pbix.

https://1drv.ms/u/s!ApgeWwGTKtFdhkQ4g8ZDs2ScOAAU?e=v5fFce

Thanks!

[v-xuding-msft]

Hi @o59393 ,

Do you mean like this?

Count rows distinct_Attribute 1 = 
COUNTROWS (
    FILTER (
        GROUPBY ( Test111, Test111[Code], Test111[Attribute] ),
        Test111[Attribute] = "Attribute 1"
    )
)

Distinct count formulas_Parameters = 
CALCULATE (
    DISTINCTCOUNT ( Test111[Code] ),
    FILTER ( Test111, Test111[Value] = Parameter[Parameter Value] )
)

If I misunderstand, please share a table or a screenshot of the expected result.

[o59393]

Hi @v-xuding-msft 

Looks good!

Just one thing. On your second dax, the countdistict should only be for Test111[Attribute] = "Attribute 1. 

Is this ok?

Distinct count formulas_Parameters = 
CALCULATE (
    DISTINCTCOUNT ( Test111[Code] ),
    FILTER ( Test111, Test111[Value] = 0 ),  Test111[Attribute] = "Attribute 1"
)

Thanks!

[v-xuding-msft · Accepted solution]

Hi @o59393 ,

Please try like this:

Distinct count formulas_Parameters =
CALCULATE (
    DISTINCTCOUNT ( Test111[Code] ),
    FILTER ( Test111, Test111[Value] = 0 && Test111[Attribute] = "Attribute 1" )
)

The conditions need to be contained in the function of FILTER and use && to connect them.

Reference:

DAX operators 

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.