Help with Filter + Distinct

[benalfassy]

Hi,

I have table with columns "status","ID"

I need to count the rows that have "Active" status but with distinct ID and then put it into a new measure.

How can i filter my table on "Active" status and then remove the non distinct rows + sum the result?

Thanks!

[v-caliao-msft · Accepted solution]

@benalfassy,

Please use the DAX below.

Measure 2 = CALCULATE(DISTINCTCOUNT(Table1[ID]),FILTER(Table1,Table1[Status]="Active"))

Regards,

Charlie Liao

[Omega]

Can you please attach the table that you are using as your dataset along with the expected results? 

[benalfassy]

ID	Status
1	Active
1	Active
1	Not Active
2	Active
3	Not Active
2	Active

i want to add new measure with value that based on the table above will be 2, because after i filter the Active and remove distinct i will have only 2 rows.

[Ashish_Mathur · Accepted solution]

Hi,

Try this

=CALCULATE(DISTINCTCOUNT(Data[ID]),Data[Status]="Active")

Hope this helps.

[Omega]

Try: 

Measure = CALCULATE(COUNTROWS(DISTINCT(Table1[ID])),FILTER(Table1,Table1[Status]="Active"))

[benalfassy]

I cant DISTINCT first because i might left with ID that have "Not Active" status although there is ID with Active status

[v-caliao-msft · Accepted solution]

@benalfassy,

Please use the DAX below.

Measure 2 = CALCULATE(DISTINCTCOUNT(Table1[ID]),FILTER(Table1,Table1[Status]="Active"))

Regards,

Charlie Liao