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 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 NAMEREFRESH DATE
Critical_Screen31/12/2024 09:34
Critical_Screen31/12/2024 09:36
HAMAL31/12/2024 09:35
HAMAL31/12/2024 09:53
ServiceCalls31/12/2024 09:45
ServiceCalls31/12/2024 09:50
ServiceCalls31/12/2024 09:55
  • 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.

     

3 Replies

  • DataNinja777's avatar
    DataNinja777
    Super 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.