Forum Discussion

Tarte's avatar
Tarte
New Member
2 years ago
Solved

DAX command for MIN MAX in horometer

Hi everyone,

 

I have a table containing meter readings for several machines:

MachineDatehour
A01/07/20241000
A10/07/20241100
A23/07/20241150
B09/07/2024200
B01/08/2024300
B20/08/2024350


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

MachineDatehourMinMonthMaxMonth
A01/07/2024100010001150
A10/07/2024110010001150
A23/07/2024115010001150
B09/07/2024200200200
B05/08/2024300300350
B20/08/2024350300350

 

 

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

DAX
horometer
use rates
  • Greg_Deckler's avatar
    Greg_Deckler
    2 years ago

    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

     

  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.