Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Count with conditions

Hi,   Need to count the 'carerid' column entries with some conditions applied based on column 'startdate'.   Conditions Every carerid is counted at least once. Only count carerid more than onc...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    5 years ago

    Anonymous 

    updated the DAX,

    please check if it's correct now

    Column = 
VAR _minindex=minx(FILTER('powerbi114 holidays','powerbi114 holidays'[carerid]=EARLIER('powerbi114 holidays'[carerId])),'powerbi114 holidays'[Index])
return  if('powerbi114 holidays'[Index]=_minindex,1,if('powerbi114 holidays'[startdate]-MAXX(FILTER('powerbi114 holidays','powerbi114 holidays'[Index]=EARLIER('powerbi114 holidays'[Index])-1),'powerbi114 holidays'[startdate])>1,1,0))
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

Anonymous , try like

 

Test = CALCULATE(COUNTX(filter(values('powerbi114 holidays'[carerId]),DATEDIFF(min('powerbi114 holidays'[startDate]),max('powerbi114 holidays'[startDate]),DAY) > 1),[carerId]))

 

or

 

Test = CALCULATE(COUNTX(filter(values('powerbi114 holidays'[carerId]),DATEDIFF(min('powerbi114 holidays'[startDate]),max('powerbi114 holidays'[startDate]),DAY) > 1),[carerId]), allexcept('powerbi114 holidays'[carerId]))