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.

     



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)

 

 

  • pedrohenriquewe's avatar
    pedrohenriquewe
    Regular Visitor
    3 years ago

    Hello! 

    "Right Before" means the last Status before "Resolved" must be "UP". It does not matter if it was 10 days ago or 1000 days ago.

    The measure have to count just two lines (Distinctcount by key).

    GCL-2379 UP16/03/2023
    GSX-2137 UP20/03/2023
    • AlanFredes's avatar
      AlanFredes
      Resolver IV
      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.