topic Re: DAX FORMULA MAX VALUES ON MULTIPLE CATEGORIES in DAX Commands and Tips

DAX FORMULA MAX VALUES ON MULTIPLE CATEGORIES

yanivsab — Wed, 01 Jan 2025 10:54:02 GMT

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

Re: DAX FORMULA MAX VALUES ON MULTIPLE CATEGORIES

DataNinja777 — Wed, 01 Jan 2025 12:07:49 GMT

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,

Re: DAX FORMULA MAX VALUES ON MULTIPLE CATEGORIES

yanivsab — Tue, 14 Jan 2025 14:16:31 GMT

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!

Re: DAX FORMULA MAX VALUES ON MULTIPLE CATEGORIES

Anonymous — Wed, 15 Jan 2025 07:49:59 GMT

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.