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.

wangjuan303's avatar
wangjuan303
Helper III
3 years ago

Thank you for your quick help, this measure does not work for my scenario, in real data sources, it also exists ID not Reject and also not Approve, for example my screenshot ID 6, I need create measure like under hightlight column, just count ID not exist Reject status and when it is apprve. ryan_mayu 

 

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    3 years ago

    wangjuan303 

    then what about this measure

    Measure =CALCULATE(DISTINCTCOUNT('Table'[ID]),FILTER('Table','Table'[status]="approve"))-CALCULATE(DISTINCTCOUNT('Table'[ID]),FILTER('Table','Table'[status]="reject"))