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.

Omega's avatar
Omega
Impactful Individual
8 years ago

Try: 

 

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

  • benalfassy's avatar
    benalfassy
    Regular Visitor
    8 years ago

    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's avatar
      v-caliao-msft
      Microsoft Employee
      8 years ago

      benalfassy,

       

      Please use the DAX below.

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

       

      Regards,

      Charlie Liao