Forum Discussion

pedrohenriquewe's avatar
pedrohenriquewe
Regular Visitor
3 years ago
Solved

Create a measure - help!

I have two tables: AllIssues: key   Resolved GCL-2379   11/04/2023 GCL-2397   27/03/2023 GSX-2137   27/03/2023 AllHistory: key Status   History New Value Start GCL-...
  • AlanFredes's avatar
    AlanFredes
    3 years ago

    Ok,
    Finally got the question.
    So the below DAX function works. Same assumption as before regarding relationships between both tables.

    Total UP2 =
    VAR Sum_Table =
    FILTER(
        SUMMARIZE(
            AllIssues,
            AllIssues[key],
            AllIssues[Resolved],
            "Last Date",CALCULATE(MAX(AllHistory[History]),FILTER(AllHistory,AllHistory[History]<AllIssues[Resolved])),
            "Last Status",LOOKUPVALUE(AllHistory[Status],AllHistory[History].[Date],CALCULATE(MAX(AllHistory[History]),FILTER(AllHistory,AllHistory[History]<AllIssues[Resolved])))
        ),
        [Last Status]="UP"
    )
    RETURN
    COUNTROWS(Sum_Table)

    The summarize function creates the table you see beside the card (where the Total UP 2 is displayed). This summarized tables look for the last date before the 'resolved' date and then does a lookup for that status. Complete function then does a count row filtering un Status=UP.

     



AlanFredes's avatar
AlanFredes
Resolver IV
3 years ago

If both tables have a relationship based on the "key" columns then a simple measure should be sufficient:
Count_UP=

CALCULATE(
    COUNTROWS(AllHistory),
    AllHistory[Status]=" UP",
    AllHistory[History]<=MAX(AllIssues[Resolved])
)



When added to a table based on AllIssues columns the rowcontext should identify each Resolved date by row. See bellow Picture.

 

  • AlanFredes's avatar
    AlanFredes
    Resolver IV
    3 years ago

    Please mark it if it works.
    Regards,

    • pedrohenriquewe's avatar
      pedrohenriquewe
      Regular Visitor
      3 years ago

      It doesn't work because I don't want all the "UP" statuses, I just want it to count when the UP status is immediately before the resolved date.

      For example, for key GCL-2397, it will not increase my measure because the status right before 'resolved date' is 'LT' not 'UP'.

      • AlanFredes's avatar
        AlanFredes
        Resolver IV
        3 years ago

        Hi Pedro,

        This should work. Assuming again there is an active relationship between both tables based on "Key" column. 
        I defined the right before 'resolve date' as trailing 10 days. You can modify it as you see fit.

        Total UP =
        VAR Sum_Table =
        FILTER(
            ADDCOLUMNS(AllHistory,
            "To_Resolve",DATEDIFF(AllHistory[History],RELATED(AllIssues[Resolved]),DAY)
            ),
            AllHistory[Status]=" UP" && [To_Resolve]>0 && [To_Resolve]<10
        )
        RETURN
        COUNTROWS(Sum_Table)