DAX FORMULA MAX VALUES ON MULTIPLE CATEGORIES

[yanivsab]

Hi,

I have a log table that contain reports name and their last refresh date/time.

The table contain multiple dates for each report.

i would like your help to achieve 2 goals : 

1. find the latest refresh for each report

2. create a card that show "x" if one of the reports last refreshed above over 30 mintues , otherwise show "ok"

Table for example:

REPORT NAME	REFRESH DATE
Critical_Screen	31/12/2024 09:34
Critical_Screen	31/12/2024 09:36
HAMAL	31/12/2024 09:35
HAMAL	31/12/2024 09:53
ServiceCalls	31/12/2024 09:45
ServiceCalls	31/12/2024 09:50
ServiceCalls	31/12/2024 09:55

[DataNinja777]

Hi @yanivsab ,

To achieve the two goals in Power BI, use the following approach:

To find the latest refresh for each report, create a calculated column or measure. For a calculated column, use the following DAX:

Latest Refresh = 
CALCULATE(
    MAX('Table'[REFRESH DATE]),
    ALLEXCEPT('Table', 'Table'[REPORT NAME])
)

Alternatively, for a measure:

Latest Refresh Measure = 
MAX('Table'[REFRESH DATE])

This will display the latest refresh date for each report when used in a table visual grouped by REPORT NAME.

To create a card that displays "x" if one of the reports has not been refreshed within the last 30 minutes, define the following DAX measure:

Refresh Status = 
VAR CurrentTime = NOW()
VAR LatestRefreshes = 
    SUMMARIZE(
        'Table', 
        'Table'[REPORT NAME], 
        "LatestRefresh", MAX('Table'[REFRESH DATE])
    )
VAR CheckIfOlder = 
    COUNTROWS(
        FILTER(
            LatestRefreshes, 
            DATEDIFF([LatestRefresh], CurrentTime, MINUTE) > 30
        )
    )
RETURN 
    IF(CheckIfOlder > 0, "x", "ok")

Add this measure to a card visual in Power BI to display either "x" or "ok" depending on whether any report's latest refresh exceeds the 30-minute threshold.

Best regards,

[yanivsab]

Hi

the Solution you suggest can't work since for each report_name i have multiple rows - so your formulas are not valid

i will appriciate if you can think of a nother way.

thanks!

[Anonymous · Accepted solution]

Hi @yanivsab ,

I suggest you to try code as below to create measures.

Latest Refresh = 
CALCULATE(
    MAX('Table'[REFRESH DATE]),
    ALLEXCEPT('Table', 'Table'[REPORT NAME])
)

Last Refresh Over 30 mins =
VAR _LastRefresh = [Latest Refresh]
VAR _BeforeLastRefresh =
    MAXX (
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[REPORT NAME] = MAX ( 'Table'[REPORT NAME] )
                && [REFRESH DATE] < [Latest Refresh]
        ),
        'Table'[REFRESH DATE]
    )
VAR _SUMMARIZE =
    SUMMARIZE (
        'Table',
        'Table'[REPORT NAME],
        "If",
            VAR _DateDiff =
                DATEDIFF ( _BeforeLastRefresh, _LastRefresh, MINUTE )
            RETURN
                IF ( _DateDiff > 30, 1, 0 )
    )
RETURN
    IF ( SUMX ( _SUMMARIZE, [If] ) = 0, "OK", "X" )

Result is as below.

Best Regards,
Rico Zhou

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