Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
PhillipC1
Helper I
Helper I

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 with what we would expect to look like. 

 

  • IBT -1: Pull first occurrence of date. if field isblank and Moderate <>"" or High <>"" or Critical <>"" then N/A
  • IBT- 2: Pull second occurrence of date. if field isblank and Moderate <>"" or High <>"" or Critical <>"" then N/A
  • IBT- 3: Pull third occurrence of date. if field isblank and Moderate <>"" or High <>"" or Critical <>"" then N/A
  • Moderate: Pull fourth occurrence of date or if "Scoring Category" = "Moderate". if field isblank and High <>"" or Critical <>"" then N/A
  • High: Pull fifth occurrence of date or if "Scoring Category" = "High". if field isblank and Critical <>"" then N/A
  • Critical: Pull sixth occurrence of date or if "Scoring Category" = "Critical"

 

Raw Data Source

ID #ACTIVITY_DATESCORING CATEGORYEVENT
881976/10/2024 1
376/18/2024 1
505077/1/2024CRITICAL 
881977/3/2024 1
881977/5/2024 2
377/5/2024MODERATE 
505077/5/2024 2
881977/8/2024 1
881977/12/2024 1
881977/30/2024 1
378/1/2024 1
881978/1/2024 1
505078/5/2024Moderate 

 

Expected outcome in new table

ID #LocationStatusNameTypeIBT-1IBT-2IBT-3ModerateHighCritical
881971ATest 1N6/10/20247/3/20247/5/20247/8/20247/12/20247/30/2024
372ATest 2CM6/18/2024N/AN/A7/5/20248/1/2024 
505073ATest 3CDN/AN/AN/AN/AN/A7/1/2024

 

There are some rows of data in the first table that should not be reflected in the new table as they would not be applicable due to them already meeting the final column "Critical".

 

I have been banging my head on this one for a bit as this is a pretty complex request, and I feel like I have the logic sound as well as what I could do, but I think I am just missing some threads to tie it all together. 

1 ACCEPTED SOLUTION
kushanNa
Frequent Visitor

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_0-1723910977053.png

 

kushanNa_1-1723911031531.png

 

View solution in original post

7 REPLIES 7
kushanNa
Frequent Visitor

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_0-1723910977053.png

 

kushanNa_1-1723911031531.png

 

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. 

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
    )

 

 

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:

PhillipC1_0-1724079844007.png

What should show:

PhillipC1_1-1724080401899.png

 

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

lbendlin
Super User
Super User

 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)

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.