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 

     

     

lbendlin's avatar
lbendlin
Super User
2 years ago
 if field isblank

please elaborate. What field?

 

Your sample data is missing the Location, Status, NameType columns. Are these not important?

 

Note that Power Query is case sensitive.  MODERATE is not the same as Moderate.  (doesn't matter in DAX)

PhillipC1's avatar
PhillipC1
Helper I
2 years ago

If field is blank or more stating that if that field, let's use IBT-1 for example, is blank. Essentially this is a stair step process meaning if you show up on this report the date for the row you show up on will go to IBT-1, then if you show up again you go to IBT-2, etc. The exception is if you have the result of "Moderate", "High", or "Critical"  as a value under "Scoring Category" then you would skip all previous steps meaning they would result in "N/A".

 

The data points not provided (location, name, etc.)  are not important for this example as my concern is more so around how to get the logic for the remaining fields. 

Whoops, yep, sorry I wrote the example wrong but the actual format will match in  prod.