Forum Discussion

yanivsab's avatar
yanivsab
Frequent Visitor
1 year ago
Solved

DAX FORMULA MAX VALUES ON MULTIPLE CATEGORIES

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 la...
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

     

DataNinja777's avatar
DataNinja777
Icon for Super User rankSuper User
1 year ago

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's avatar
yanivsab
Frequent Visitor
1 year ago

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's avatar
    Anonymous
    Not applicable
    1 year ago

    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.