Forum Discussion

PowerrrBrrr's avatar
PowerrrBrrr
Icon for Helper III rankHelper III
4 years ago
Solved

Get the category with maximum value

I have below table and I am looking to get the category with Maximum value Table A             Id                         Machines             1                         MachineA     ...
Need Help
  • Jos_Woolley's avatar
    Jos_Woolley
    4 years ago

    Hi,

    MachineMaxPressure =
VAR LatestDate =
    MAXX ( 'Table C', INT ( 'Table C'[Timestamp] ) )
VAR MaxPressureonLatestDate =
    CALCULATE (
        MAXX (
            'Table C',
            IF (
                INT ( 'Table C'[Timestamp] ) = LatestDate,
                IF ( RELATED ( 'Table B'[Description] ) = "Pressure rate", 'Table C'[Value] )
            )
        )
    )
VAR TimeSeriesIDMaxPressureonLatestDate =
    LOOKUPVALUE (
        'Table C'[TimeseriesId],
        'Table C'[Value], MaxPressureonLatestDate
    )
VAR IDMaxPressureonLatestDate =
    LOOKUPVALUE (
        'Table B'[ID],
        'Table B'[timeseriesId], TimeSeriesIDMaxPressureonLatestDate
    )
VAR MachineMaxPressureonLatestDate =
    LOOKUPVALUE ( 'Table A'[Machines], 'Table A'[Id], IDMaxPressureonLatestDate )
RETURN
    MachineMaxPressureonLatestDate

    Regards

v-xiaotang's avatar
v-xiaotang
Icon for Community Support rankCommunity Support
4 years ago

Hi PowerrrBrrr 

Thanks for reaching out to us.

 

You can use the measure, I also create a sample for your reference, file attached bellow.

MachineName = 
    var _maxDate=CALCULATE(MAX('Table C'[Timestamp]),ALL('Table C'))
    var _maxValue=MAXX(FILTER('Table C', 'Table C'[Timestamp]=_maxDate && RELATED('Table B'[Description]) = "Pressure rate"),[Value])
    var _maxTSID=CALCULATE(MAX('Table C'[TimeseriesId]),'Table C'[Value]=_maxValue)
    var _maxID=CALCULATE(MAX('Table B'[ID]),'Table B'[timeseriesId]=_maxTSID)
    var _Machine=CALCULATE(MAX('Table A'[Machines]),'Table A'[Id]=_maxID)
return _Machine

Kindly Note: the type of ID, TimeseriesId in my sample is Text, so the measures are fit with them.

result

 

 

Best Regards,

Community Support Team _Tang

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