topic Re: DAX command for MIN MAX in horometer in DAX Commands and Tips

DAX command for MIN MAX in horometer

Tarte — Thu, 08 Aug 2024 16:31:09 GMT

Hi everyone,

I have a table containing meter readings for several machines:

Machine	Date	hour
A	01/07/2024	1000
A	10/07/2024	1100
A	23/07/2024	1150
B	09/07/2024	200
B	01/08/2024	300
B	20/08/2024	350

I would like a DAX measure that could find me the MAX and MIN of the meters for each month for the different machines:

Machine	Date	hour	MinMonth	MaxMonth
A	01/07/2024	1000	1000	1150
A	10/07/2024	1100	1000	1150
A	23/07/2024	1150	1000	1150
B	09/07/2024	200	200	200
B	05/08/2024	300	300	350
B	20/08/2024	350	300	350

I have tried to do this formula but due to the large number of rows in the starting table I have resource errors displaying the measure:

MinMonth =

CALCULATE(

MIN(hour),

FILTER(

ALL(table),

Machine = MAX(Machine) &&

YEAR(Date) = YEAR(MAX(Date)) &&

MONTH(Date) = MONTH(MAX(Date))

)

thank you in advance

Re: DAX command for MIN MAX in horometer

Greg_Deckler — Thu, 08 Aug 2024 17:30:54 GMT

Tarte What happens if you ditch the ALL in your filter clause?

Calculate = VAR __Machine = MAX( 'Table'[Machine] ) VAR __Date = MAX( 'Table'[Date] ) VAR __Result = CALCULATE( MAX( 'Table'[hour] ), 'Table'[Machine] = __Machine, YEAR( 'Table'[Date] ) = YEAR( __Date ), MONTH( 'Table'[Date] ) = MONTH( __Date ) ) RETURN __Result

Also, here is a No CALCULATE approach:

NC = VAR __Machine = MAX( 'Table'[Machine] ) VAR __Date = MAX( 'Table'[Date] ) VAR __Table = FILTER( ALLSELECTED( 'Table' ), [Machine] = __Machine && YEAR( [Date] ) = YEAR( __Date ) && MONTH( [Date] ) = MONTH( __Date ) ) VAR __Result = MAXX( __Table, [hour] ) RETURN __Result

Re: DAX command for MIN MAX in horometer

Anonymous — Mon, 12 Aug 2024 06:41:33 GMT

Hi Tarte

Thanks for the reply from Greg_Deckler.

The following measures are for your reference.

MinMonth = CALCULATE(MIN([hour]), ALLEXCEPT('Table', 'Table'[Machine]), MONTH('Table'[Date]) = MONTH(MAX([Date])))

MaxMonth = CALCULATE(MAX([hour]), ALLEXCEPT('Table', 'Table'[Machine]), MONTH('Table'[Date]) = MONTH(MAX([Date])))

Output:

Best Regards,
Yulia Xu

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