Best Employee of the month

[Anonymous]

Hi all,

I am stuck with a logic in powerBi dekstop. I am trying to write a measure that will display performer of the month's name on a show card. There are four columns to compare. Attaching the screen shot for your reference. I am writing a If condition with AND logic. 

IF(waiting for task = Min(Total task) && Total hours = Max(total hours) && QA miss = 0 && Process Bug = 0, "it should display the name", else "-"). Can someone please help me with DAX measure logic. 

Here I want to first sum the total hrs eg. ABC total hrs is 5hrs it should then compare with the max value in the list like

        Total Hrs

ABC      5

DEF      9

DEF      9

ABC should be then compared with the max value in the list similar for Total task it should be min & QA Miss & Process debug 

Help would be appreciated.

Thanks in advance!

[Anonymous · Accepted solution]

Hi @Anonymous ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

1. Create two measures as below to get the sum of hours and tasks:

Sum of hours = 
CALCULATE (
    SUM ( 'Table'[Total Hrs] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Name] = SELECTEDVALUE ( 'Table'[Name] )
    )
)

Sum of tasks = 
CALCULATE (
    SUM ( 'Table'[Total Task] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Name] = SELECTEDVALUE ( 'Table'[Name] )
    )
)

2. Create a measure as below to judge if the name fill all conditions, if yes then 1 else 0

Flag = 
VAR _selname =
    SELECTEDVALUE ( 'Table'[Name] )
VAR _maxhours =
    MAXX ( ALLSELECTED ( 'Table' ), [Sum of hours] )
VAR _mintask =
    MINX ( ALLSELECTED ( 'Table' ), [Sum of tasks] )
VAR _qa =
    CALCULATE (
        SUM ( 'Table'[QA] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Name] = _selname )
    )
VAR _process =
    CALCULATE (
        SUM ( 'Table'[Process] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Name] = _selname )
    )
RETURN
    IF (
        [Sum of tasks] = _mintask
            && [Sum of hours] = _maxhours
            && _qa = 0
            && _process = 0,
        1,
        0
    )

3. Create a table visual and apply a visual-level filter with the condition (Flag is 1)

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Dhacd]

I am sorry, that is not how and condition in dax works. 

[Dhacd]

In this scenario what if an employee has total hours max but that employee has total task minimum and QA and Process are also not zero? Should we go for the second employee with max total hours? 
In my opinion, this is not the correct way to find the best employee. Each matric should be given a weightage and a final KPI should be calculated by dividing the matrics with the weightage and adding those together. And With that Final KPI we can take the max for the best employee.

Discuss this with your management.

If the logic which I mention is what you need it is possible. Reply if you want a solution.

Thanks and regards,
Atma.

[Anonymous]

But it will check for the next max condition right since we are using AND conditin it will reflect the value only if all the conditions are staisified and for judging the performance all the parameters needs to follow.