Forum Discussion

Muhannadtaghi's avatar
Muhannadtaghi
Frequent Visitor
6 years ago

DAX - How to Count the Consecutive Occurrences

Hi There; I would like to count the consecutive worked days for a group of employees, I have a table of employee work date, employee ID, employee name as shown below and I wont to identify who worke...
mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

Did you make a 1:Many relationship between WorkDates and Work tables?

 

Regards,

Pat

 

Muhannadtaghi's avatar
Muhannadtaghi
Frequent Visitor
6 years ago

Hi mahoneypat; 

Thanks for your reply, I tried all 4 different types of cardinalities and all types of cross filter directions, but still showing blank.

Also, I used the DAX in measure and calculation column, but nothing changed.

Please advise.

Many thanks, 

 

RelationshipBlank

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    My bad for not clarifying I was using the measure in a table with WorkDates[Date] and Work[EmployeeID].  To use it in a card, use this measure that references the first one.

     

    Total Consecutive Days =
    SUMX (
    SUMMARIZE ( 'Work', WorkDates[Date], 'Work'[Employee ID] ),
    [Number Consecutive Days]
    )

     

    And the relationship needs to be changed back too.  1:Many from WorkDates to Work (Filter going 1:many too).

     

    If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

    • Muhannadtaghi's avatar
      Muhannadtaghi
      Frequent Visitor
      6 years ago

      Hi Pat;

      Thanks for the comment, could you please share with me the pbix file so I can see how you did it?

      Many thanks for the support, 

      • lstevens's avatar
        lstevens
        Regular Visitor
        5 years ago

        Did this solution ever work?