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

 

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

7 Replies

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

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