Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Using DAX to create flags based on status and date

I am looking to create three difeerent columns with flags based on the below conditions. The trick comes when the flag should show on rows, even though the value os not there. For example (ID: 19 & 53 below should show "Job Complete" on all rows since the status of '10' is now active.)

 

If an ID has ever received Status Code 10 then a “Job Completed” flag is applied to all instances of the ID (even on rows that don’t fulfill this requirement), else Not Complete

 

If an ID has received Status Codes 30, 60, or 280 and last modified time is within 48 hours of current time then a “Touched” flag is applied to all instances of the dispatch ID (even on rows that don’t fulfill this requirement), else Needs Attention

 

If an ID has NOT received Status Code 10 and 5 hours have passed since most recent last modified timestamp then a "still open - Exceeds guidelines" flag is applied to all instances of the dispatch ID (even on rows that don’t fulfill this requirement); otherwise "Still Open - Within Guidelines"

 

Here are how the tables look: 

 

 

IDSTATUS_CODELAST_MODIFIED DATE
1930

1/20/2020

53

 

301/21/2020

93

60

1/20/2020

19

10

1/28/2020

50

280

1/1/2020

53

10

1/27/2020

 

IDDispatch TimestampTrade CodeTrade_Day Flag
532/14/2020ASW16
501/26/2020WQA5
1912/7/2019YTR4
9311/30/2019OIP8
2 REPLIES 2
danextian
Super User
Super User

Hi @Anonymous 
Your sample data appears to be incomplete (lacking some scenarios as per description of what you are trying to achieve). It also doesn't have timestamps for computing the number of hours lapsed and the basis of your "current time" like refresh time, etc. So I made do of what is available. Try this:

Status =
VAR __10 =
    CALCULATE (
        COUNTA ( 'Table'[ID] ),
        ALLEXCEPT ( 'Table', 'Table'[ID] ),
        'Table'[STATUS_CODE] = 10
    )
VAR __3060280 =
    CALCULATE (
        COUNTA ( 'Table'[ID] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[ID] = EARLIER ( 'Table'[ID] )
                && ( 'Table'[STATUS_CODE] = 30
                || 'Table'[STATUS_CODE] = 60
                || 'Table'[STATUS_CODE] = 280 )
        )
    )
VAR __LATEST_MODIFIED =
    CALCULATE (
        MAX ( 'Table'[LAST_MODIFIED DATE] ),
        ALLEXCEPT ( 'Table', 'Table'[ID] )
    )
VAR __TIME_LAPSED =
    DIVIDE ( DATEDIFF ( __LATEST_MODIFIED, NOW (), MINUTE ), 60 )
RETURN
    SWITCH (
        TRUE (),
        __10 >= 1, "Job Completed",
        __3060280 >= 1
            && __TIME_LAPSED <= 48, "Touched",
        __3060280 >= 1
            && __TIME_LAPSED > 48, "Needs Attention",
        ISBLANK ( __10 )
            && __TIME_LAPSED >= 5, "Still open - Exceeds Guidelines",
        "Still Open - Within Guidelines"
    )

  






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.
Anonymous
Not applicable

Hi @danextian 

 

Thank you for your reply! I updated the description to include the data table examples. 

 

I ended up created separate columns for each flag. I'm having problems creating flags for these two conditions:

 

If status code is null when [now - dispatch timestamp] >= 24 hours “Unscheduled”

 

If a dispatch ID has NOT received Status Code 10 and "x" hours have passed since most recent last status timestamp then a "Still open - Exceeds guidelines" flag is applied to all instances of the dispatch ID (even on rows that don’t fulfill this requirement); otherwise "Still Open - Within Guidelines"

The "X" value changes based on conditional column "Trade_Day Flag"

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.