Forum Discussion

benalfassy's avatar
benalfassy
Regular Visitor
8 years ago
Solved

Help with Filter + Distinct

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 "Activ...
Need Help
  • v-caliao-msft's avatar
    v-caliao-msft
    8 years ago

    benalfassy,

     

    Please use the DAX below.

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

     

    Regards,

    Charlie Liao

  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    Hi,

     

    Try this

     

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

     

    Hope this helps.

benalfassy's avatar
benalfassy
Regular Visitor
8 years ago
ID Status  
 1 Active    
 1Active  
1Not Active    
2Active    
3Not Active    
2Active   

 

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's avatar
Ashish_Mathur
Super User
8 years ago

Hi,

 

Try this

 

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

 

Hope this helps.