Forum Discussion

wangjuan303's avatar
wangjuan303
Helper III
3 years ago
Solved

How to filter data from Dax

I have a report like under the screenshot, ID will have a different status,

Want to create a measure to count ID that not including a "Reject" status record, 

The highlighted column is what I need, but from Dax, I don't know how to add conditions, 

Thank you for your help. 

 

 

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    3 years ago

    wangjuan303 

    maybe you can try this

     

    approve count = 
VAR _reject=countx(FILTER(all('Table'),'Table'[ID]=max('Table'[ID])&&'Table'[status]="reject"),'Table'[ID])
return if(ISBLANK(_reject),1)

    pls see the attachment below

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    3 years ago

    Hi,

    This measure works

    Measure = COUNTROWS(FILTER(VALUES('Table'[ID]),CALCULATE(COUNTROWS('Table'),'Table'[status]="approve")>=1&&CALCULATE(COUNTROWS('Table'),'Table'[status]="reject")=0))

    Hope this helps.

8 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    3 years ago

    wangjuan303 

    maybe you can try this

     

    approve count = 
VAR _reject=countx(FILTER(all('Table'),'Table'[ID]=max('Table'[ID])&&'Table'[status]="reject"),'Table'[ID])
return if(ISBLANK(_reject),1)

    pls see the attachment below

     

    • wangjuan303's avatar
      wangjuan303
      Helper III
      3 years ago

      ryan_mayu Hi Ryan, Thank you for your help. But I think I give a wrong example, make you misunderstand, I need count of "Approve" status without Reject ID, for example, I need under result, Do you have any idea? Thank you very much