DISTINCT VALUE

[karkar]

Hello,

How can I get unique value per ID from the below example? Currently it is giving the count of meas_value.

i tried to change it to Calculate(DISTINCTcount(Query1[value]), in the below formulae and it did not work

Total Count = Calculate(count(Query1[value]),

                       Filter(ALL(Query1[Admission_Hours]),Query1[Admission_Hours] >72))

ID     value

101    0

101    0

101    0

101    0

101    0

101    0

101    0

Currently the output is (since its counting 7 times)

ID    Value

101   7

WANT:

ID   Value

101   0

[MFelix]

Hi @karkar,

You should do the distinct count on the ID column not on the value because it's what you want to count not the value column.

Regards,
MFelix

[karkar]

Like THIS?

Total Count = Calculate(DISTINCTcount(Query1[ID]),

                       Filter(ALL(Query1[Admission_Hours]),Query1[Admission_Hours] >72))

But I ALSO WANT TO SHOW THE UNIQUE "VALUES" AN id CONTAINS

[karkar]

Currently the output is (since its counting 7 times)

ID    totalcount

101   7

WANT:

ID   totalcount

101   0      (DISTINCT VALUE FOR EACH 'ID')

[v-yuezhe-msft · Accepted solution]

@karkar,

Please check if the following DAX returns your expected result.

Total Count = Calculate(max(Query1[value]),Filter(ALL(Query1[Admission_Hours]),Query1[Admission_Hours] >72))

Regards,