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

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!