Count with conditions

[Qotsa]

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 once if there is greater than 1 day between the next 'startdate' for that 'carerid'.

I have tried this measure but it doesn't return any results and I do understand why as it is comparing the same values.

Test = CALCULATE(COUNT('powerbi114 holidays'[carerId]),DATEDIFF('powerbi114 holidays'[startDate],'powerbi114 holidays'[startDate],DAY) > 1)

[ryan_mayu]

@Qotsa 

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

[Qotsa]

@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

[ryan_mayu]

@Qotsa 

here is a wordaround for you.

1. create an index column in PQ

2. use DAX to create a column

Column = 
VAR _minindex=minx(FILTER('Table','Table'[careid]=EARLIER('Table'[careid])),'Table'[Index])
return if('Table'[Index]=_minindex,1,if(MAXX(FILTER('Table','Table'[Index]=EARLIER('Table'[Index])+1),'Table'[startdate])-'Table'[startdate]>1,1,0))

[Qotsa]

Hi,

Thanks both for your replies.

Not getting the desired results with your formulas.

I have uploaded the pbix here

if you could look I would appreciate it.

[ryan_mayu · Accepted solution]

@Qotsa 

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))

[Qotsa]

@ryan_mayu 

Thank you. works great.

@amitchandak 
Thanks for your help also.

[amitchandak]

@Qotsa , 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]))