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

 

 

 

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    5 years ago

    Anonymous 

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

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