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:

keyStatus  History New Value Start
GCL-2379 UP15/03/2023
GCL-2379 BL15/03/2023
GCL-2379 UP16/03/2023
GCL-2379 END11/04/2023
GCL-2397 UP23/03/2023
GCL-2397 LT24/03/2023
GCL-2397 END27/03/2023
GSX-2137 UP20/03/2023
GSX-2137 END27/03/2023


I wanna count the number of keys that was "Status" = "UP" rightbefore the date of "Resolved" (table AllIssues). It seems to be easy but i am stuck =(

For the example above, the measure output would be 2, because only GSX-2137 and GCL-2379 was "UP" right before the date of 'Resolved'.

  • 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.

     



6 Replies

  • 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.

     

      • 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'.