Forum Discussion

AndyDD_UK's avatar
AndyDD_UK
Icon for Helper III rankHelper III
3 years ago

Help with DAX

In my Cube table I have a table showing employee and the number of hours they have submitted late in their timesheets by week.   For example, if I filter for all the weeks in Feb 2023 I can see tha...
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

AndyDD_UK 

Great. But what is the criteria for late?

AndyDD_UK's avatar
AndyDD_UK
Icon for Helper III rankHelper III
3 years ago

Ah, sorry....there is a column that says 'Late_hours'...if the value is 0 then they are not late, if the value is over zero, then they are late.

 

So the challenge is rolling this up...for the 4 weeks in Feb 2023 they may have zero late hours for 3 weeks and more than zero in one of the weeks. If the Power BI report I want them to appear only as 'Late' if I select all 4 weeks. Thanks for your help on this.

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    AndyDD_UK 

    Have you tried Measure like

    IF ( SUM ( 'Table'[Late_hours] ) = "Not Late" )

    • PbiwithPrasad's avatar
      PbiwithPrasad
      Icon for Helper I rankHelper I
      3 years ago

      Small correction in the dax

      IF ( SUM ( 'Table'[Late_hours] ) =0, "Not Late" ,"Late")

      • AndyDD_UK's avatar
        AndyDD_UK
        Icon for Helper III rankHelper III
        3 years ago

        Thanks, but my answer is still as above. That will return "Late" or "Not Leve" at row level where each row represents one employee and week

         

        I need to summarize by employee based on the weeks selected.

    • AndyDD_UK's avatar
      AndyDD_UK
      Icon for Helper III rankHelper III
      3 years ago

      Yes, that will give me answer by week. Example for employ 123,

       

      Week 1 = Not Late

      Week 2 = Not Late

      Week 3 = Not Late

      Week 4= Late

       

      If I select all 4 weeks I want the DAX to show them as Late.

       

      Basically work want to select a bunch of weeks and see who has not filled out their timesheets on time. Imagine there are 100 employees they don't want to see the same 100 employees in the table as both Late and Not Late.. they;re either Late or On time for the period selected