cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
karkar Member
Member

DISTINCT VALUE

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: DISTINCT VALUE

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

Community Support Team _ Lydia Zhang
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
Super User

Re: DISTINCT VALUE

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




karkar Member
Member

Re: DISTINCT VALUE

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

Re: DISTINCT VALUE

Currently the output is (since its counting 7 times)

ID    totalcount

101   7

 

WANT:

ID   totalcount

101   0      (DISTINCT VALUE FOR EACH 'ID')

 

Moderator v-yuezhe-msft
Moderator

Re: DISTINCT VALUE

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.