Forum Discussion
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...
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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!
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.