CODEWITHSUNDEEP
powerbidax
niteshbarnwal
niteshbarnwal

Reputation: 49

How to get final status with filtering each date in Power Bi dax query

I have few jobs which runs on its scheduled time each day. I have logTime, Job_Name, Status. I have to get the final status as if on a particular day job is success, failed or InProgress.

Considering below, I have to get the final_status Column.

Sample Data

Upvotes: 0

Views: 352

Answers (1)

Olly
Olly

Reputation: 7891

Add a lookup table, which assigns a numeric value to each status - in ascending order:

+-----------+------------+
| status ID |   status   |
+-----------+------------+
|         1 | Succeeded  |
|         2 | Inprogress |
|         3 | Failed     |
+-----------+------------+

Create a relationship between 'status' in your job table and this status table.

Now you can add a calculated column to your job table, to calculate the max status for each day:

FinalStatus = 
    LOOKUPVALUE (
        StatusTable[status],
        StatusTable[status ID],
        MAXX ( 
            FILTER ( 
                JobTable,
                JobTable[logTime] = EARLIER ( JobTable[logTime] )
            ),
            RELATED ( StatusTable[status ID] )
        )
    )

See https://excel.solutions/so20181206_lookupstatus/ for a worked example

Upvotes: 1

Related Questions