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.