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,...
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.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
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.