Help with DAX

[AndyDD_UK]

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 that employee 123 was late for some weeks and on time for others.

In my Power BI report I want to be able to tag them as 'Late' if they were late for at least one of the weeks selected.

At the moment as my DAX is at row level, they are being tagged as both 'Late' and 'Not Late' in the report. I only want to see them tagged as 'Late' as, for the Feb 2023 period selected, they 'failed'...they are 'Late' for at least one week.

I only want to see employees with zero late hours for the period selected tagged as 'Not Late' in my report.

The key point is that I want to see employees listed only once in the report, either as 'Late' or 'Not Late', for the period of time selected.

In my Cube Table I've tried using filters, eg SELECTED VALUE and ALLEXCEPT (and others) and have not been able to solve this, what's the correct DAX to use ? 

[tamerj1]

Hi @AndyDD_UK 

How does the data look like?

[AndyDD_UK]

Sorry don't understand your question...unless this helps ?

Employee = text

Week = date

Hours = decimal to one place

Is that what you mean ?

[tamerj1]

@AndyDD_UK 

Great. But what is the criteria for late?

[AndyDD_UK]

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]

@AndyDD_UK 

Have you tried Measure like

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

[AndyDD_UK]

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

[PbiwithPrasad]

Small correction in the dax

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

[AndyDD_UK]

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.

[tamerj1]

@AndyDD_UK 

The problem is that I don't see how your visual looks like therefore I assumed that you are interested to show for each employee, the status (late or not late based on the weeks selected (from week or month slicer). In this case I guess there is no need for the weeks to exist in the table visual therefore if the sum is more than zero for any employee within the selected period that means the employee has late hours within that selected period. The simple sum measure should work just fine. If for dome reason you want to keep the week column in the table visual and you want to reflect the status of the selected period over of of the selected weeks the you can use 

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

[AndyDD_UK]

Thanks tried that as a calculated column and it's still at row level. 

My visual is a matrix. Column A is "Compliance', Column B is employee name and columns are months/weeks. 

If I create calculation as DAX measure I can't show it in Column A. Thanks, hope you can help?

[tamerj1]

@AndyDD_UK 

What is compliance? Are week and month from the same table for from a connected dimDate table?

[AndyDD_UK]

Sorry...compliance is whether they are late or not...all the data required is in the one table called EMPL in the Cube...

In EMPL you have week....and in DimDate Table there is week and month

Thanks again

[tamerj1]

@AndyDD_UK 
The compliance column must not be in the matrix as calculated columns are not dynamic, rather they're calculated once when you refresh the pbix file. 
The very first DAX I shared with you is a measure not a calculated column. And I'm pretty sure that once placed in the values of the matrix, it should work just fine at both week level and month level.

Compliance Mesure = 
IF ( SUM ( 'EMPL'[Late_hours] ) = 0, "Not Late", "Late" )