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))
ryan_mayu's avatar
ryan_mayu
Icon for Super User rankSuper User
5 years ago

Anonymous 

is your date type dd/mm/yyyy? why the result for careid 17 is 1?

Anonymous's avatar
Anonymous
Not applicable
5 years ago

ryan_mayu 

 

Yes, date is dd/mm/yyyy.

carerid = 1 because there is not greater than 1 day between each entry.


If the dates were

05/03/21

06/03/21

08/03/21

 

05 & 06 is counted as 1

08 is counted as 1 as greater than 1 day between 06 & 08.

 

the desired outcome is =2