Forum Discussion

stewartad1's avatar
stewartad1
Regular Visitor
1 year ago
Solved

Max date with conditions

Hi, 1st time poster here, hope this makes sense.

I need to retreive column "Current Risk Level" from table "RM_ImpactHistory" but with multiple conditions in place:
1. Find the max date but less than or equal to selectedDateTime, this is a variable coming from a slicer using selectedvalue

2. Risk ID being the same between these two tables: RM_ImpactAnalysisImpact and RM_ImpactHistory


Here is my current formula:

CurrRiskLevel =
MAXX(
    FILTER(RM_ImpactHistory,
    RM_ImpactHistory[RiskID] = RM_ImpactAnalysisImpact[RiskID] &&
    RM_ImpactHistory[RM_History.EventDate] <= [SelectedDateTime]
    ),
    RM_ImpactHistory[Current Risk Level]
)

Here's a picture of the report

Here's a picture of the corresponding data table

 

When 10/4/2024 4:23:33 PM is selected in the slicer, the expected result for "current risk level" should be "Medium: 12" and that is working. But when 11/6/2024 12:47:49 PM is selected in the slicer, the expected result for "current risk level" should be "Low: 8" and that is not working, it continues to show Medium: 12.

Here's a picture of the two options in the slicer

 


 

  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi stewartad1 

     

    Please try this:

    CurrRiskLevel =
VAR _MaxDate =
    MAXX (
        FILTER (
            ALLSELECTED ( RM_ImpactHistory ),
            RM_ImpactHistory[RiskID] = RM_ImpactAnalysisImpact[RiskID]
                && RM_ImpactHistory[RM_History.EventDate] <= [SelectedDateTime]
        ),
        RM_ImpactHistory[RM_History.EventDate]
    )
RETURN
    CALCULATE (
        SELECTEDVALUE ( RM_ImpactHistory[Current Risk Level] ),
        FILTER (
            ALLSELECTED ( RM_ImpactHistory ),
            RM_ImpactHistory[RM_History.EventDate] = _MaxDate
        )
    )

    Don't use aggregate functions to process text-type data, the result of the MAX(RM_ImpactHistory[Current Risk Level]) is Medium: 12 but Low: 8 because the initial M is in front of L.

     

    Hope it can help,

     

    Best Regards

    Zhengdong Xu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi stewartad1 

     

    Please try this:

    CurrRiskLevel =
VAR _MaxDate =
    MAXX (
        FILTER (
            ALLSELECTED ( RM_ImpactHistory ),
            RM_ImpactHistory[RiskID] = RM_ImpactAnalysisImpact[RiskID]
                && RM_ImpactHistory[RM_History.EventDate] <= [SelectedDateTime]
        ),
        RM_ImpactHistory[RM_History.EventDate]
    )
RETURN
    CALCULATE (
        SELECTEDVALUE ( RM_ImpactHistory[Current Risk Level] ),
        FILTER (
            ALLSELECTED ( RM_ImpactHistory ),
            RM_ImpactHistory[RM_History.EventDate] = _MaxDate
        )
    )

    Don't use aggregate functions to process text-type data, the result of the MAX(RM_ImpactHistory[Current Risk Level]) is Medium: 12 but Low: 8 because the initial M is in front of L.

     

    Hope it can help,

     

    Best Regards

    Zhengdong Xu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.