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 "Active" status and then remove the non distinct rows + sum the result?

 

Thanks!

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.

6 Replies

  • Omega's avatar
    Omega
    Impactful Individual
    8 years ago

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

    • 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"))