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

Jos_Woolley's avatar
Jos_Woolley
Icon for Solution Sage rankSolution Sage
4 years ago

Sorry, not sure I understand. Can you repost your 3 tables with an example of what you mean?

Regards

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

    Jos_Woolley Modified my question and table as requested. Please check now. You solution works when there is a value in the "Value" field but gives error when the value is blank (which can be a case some day)

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

      Hi PowerrrBrrr 

      when the value is blank in Value field, you can add a IF judgement statement,

      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 IF(ISBLANK(_maxValue)||_maxValue=0,BLANK(),_Machine)

       

       

      Regards,

      Community Support Team _Tang

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

        I am getting Blank even when the value is not zero or blank. ie. if I do 

        RETURN IF(ISBLANK(MaxPressureonLatestDate)||MaxPressureonLatestDate=0,BLANK(),MachineMaxPressureonLatestDate)

        I get error as multiple  value supplied but when I just Return MaxPressureonLatestDate,. I get value as Blank