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!