Forum Discussion

PhillipC1's avatar
PhillipC1
Helper I
2 years ago
Solved

How to pull date from another table based on complex if statement

Hello,   I am working on trying to be able to pull a date in from another table to multiple columns depending on the logic that is listed below. I have the example data sets below as well along wit...
Need Help
  • kushanNa's avatar
    kushanNa
    2 years ago

    Create a calculated column to get the occurrence 

     

    Occurrence Order = 
VAR CurrentID = 'Table'[ID]
VAR CurrentDate = 'Table'[ACTIVITY_DATE]
RETURN
    COUNTROWS(
        FILTER(
            'Table',
            'Table'[ID] = CurrentID &&
            'Table'[ACTIVITY_DATE] <= CurrentDate
        )
    )

     

    and create another calculated column to to do the grouping 

     

     

    New Column 2 = 
VAR OccurrenceNumber = 'Table'[Occurrence Order]
VAR ScoringCategory = 'Table'[SCORING CATEGORY]
RETURN
    SWITCH(
        TRUE(),
        OccurrenceNumber = 4, "Moderate",
        OccurrenceNumber = 5, "High",
        OccurrenceNumber = 6, "Critical",
        OR(ISBLANK(ScoringCategory), ScoringCategory = ""), "IBT-" & OccurrenceNumber,
        ScoringCategory
    )

     

    and use a matrix table and add the following fields 

     

     

kushanNa's avatar
kushanNa
Super User
2 years ago

Create a calculated column to get the occurrence 

 

Occurrence Order = 
VAR CurrentID = 'Table'[ID]
VAR CurrentDate = 'Table'[ACTIVITY_DATE]
RETURN
    COUNTROWS(
        FILTER(
            'Table',
            'Table'[ID] = CurrentID &&
            'Table'[ACTIVITY_DATE] <= CurrentDate
        )
    )

 

and create another calculated column to to do the grouping 

 

 

New Column 2 = 
VAR OccurrenceNumber = 'Table'[Occurrence Order]
VAR ScoringCategory = 'Table'[SCORING CATEGORY]
RETURN
    SWITCH(
        TRUE(),
        OccurrenceNumber = 4, "Moderate",
        OccurrenceNumber = 5, "High",
        OccurrenceNumber = 6, "Critical",
        OR(ISBLANK(ScoringCategory), ScoringCategory = ""), "IBT-" & OccurrenceNumber,
        ScoringCategory
    )

 

and use a matrix table and add the following fields 

 

 

PhillipC1's avatar
PhillipC1
Helper I
2 years ago

Hi kushanNa,

 

This worked great, but is there a way to limit the occurence number to never exceed 3? When I run this with a larger data set there are some occurences that come up at 7 or 8. As the source data set grows overtime I think that will continue to increase. 

  • kushanNa's avatar
    kushanNa
    Super User
    2 years ago

    Hi , add a >= 6 then it will stop from 6 , and change the matrix table values to latest so it will show the last occurrence date

     

    New Column 2 = 
VAR OccurrenceNumber = 'Table'[Occurrence Order]
VAR ScoringCategory = 'Table'[SCORING CATEGORY]
RETURN
    SWITCH(
        TRUE(),
        OccurrenceNumber = 4, "Moderate",
        OccurrenceNumber = 5, "High",
        OccurrenceNumber >= 6, "Critical",
        OR(ISBLANK(ScoringCategory), ScoringCategory = ""), "IBT-" & OccurrenceNumber,
        ScoringCategory
    )

     

     

    • PhillipC1's avatar
      PhillipC1
      Helper I
      2 years ago

      Thank you again and for the quick response. That did it! 

    • PhillipC1's avatar
      PhillipC1
      Helper I
      2 years ago

      Would there be a way to prevent an occurence number from going backwards. Sorry, I think I worded that incorrectly. Essentially looking at this below, once an ID is "Critical" then that should be the last date populated. Meaning no date after that would be placed in one of the previous columns. So by order of events (IBT-1, IBT-2,IBT-3, Moderate, High, Critical) the dates should always run from oldest to newest in the table

       

      What currently shows:

      What should show: