Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Ignore grouping if value is found

I want to write a DAX which count the rows based on the grouping columns  'id' and 'date', but at the same time want to ignore the grouping count if the value contains x.   below is the expected re...
Need Help
v-lili6-msft's avatar
v-lili6-msft
Icon for Community Support rankCommunity Support
7 years ago

hi, joepath 

If you want this measure?

msdnNewMeasure = 
var _table= SUMMARIZE('Review Metrics'
,'Review Metrics'[ORG_ENCOUNTER_KEY],'Review Metrics'[Activity Date],'Review Metrics'[NewType]
,"Review"
,IF(CALCULATE(COUNTA('Review Metrics'[Review Type]))= CALCULATE(COUNTA('Review Metrics'[Review Type]),
    FILTER('Review Metrics','Review Metrics'[Review Type]="Additional Review")),1,2))
return
COUNTROWS(FILTER(_table,[Review]=1))

and you could use this formula to add a new table to check what is the aggregate conditions of your requirement

Table = FILTER(SUMMARIZE('Review Metrics'
,'Review Metrics'[ORG_ENCOUNTER_KEY],'Review Metrics'[Activity Date],'Review Metrics'[USER_KEY],'Review Metrics'[NewType]
,"Review"
,IF(CALCULATE(COUNTA('Review Metrics'[Review Type]))= CALCULATE(COUNTA('Review Metrics'[Review Type]),
    FILTER('Review Metrics','Review Metrics'[Review Type]="Additional Review")),1,2)),'Review Metrics'[ORG_ENCOUNTER_KEY] in {10013730,10013728,10013724,10013746,10013729,10013726})

Best Regards,

Lin

 

joepath's avatar
joepath
Icon for Helper II rankHelper II
7 years ago

v-lili6-msft  This seems close to be what i am looking for, still has some issue while applying the other filter like dimuser.

I will work on that,

Thanks for your help.

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    hi, joepath 

    For the different conditional filter, the summary table will get different result.

    You could use the same way as I provided to add a new table to check the logic.

    Hope it could help you.

     

    Best Regards,

    Lin

  • joepath's avatar
    joepath
    Icon for Helper II rankHelper II
    7 years ago

    v-lili6-msft  Or can we write a logic something like, create a flag in the review table and based on the grouping (activity date,user key,and org encounter key) if in the group any value found with Initial review then update all the additional review row flag with 0 and if we don't find any value in the group then update the flag with 1, and in the calculation we can use that flag to count the additional review. that will not be affected by the filter condition.

     

    In Dax what are the function we can use to archive this?