Forum Discussion
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...
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
Super User
Super UserAnonymous
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))
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
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))