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

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
    • v-xiaotang's avatar
      v-xiaotang
      Icon for Community Support rankCommunity Support
      4 years ago

      Hi PowerrrBrrr 

      “I am getting Blank even when the value is not zero or blank”

      Kindly share the screenshot of your test result, we'll check the sample file.

       

      BTW, in my side, the sample file works fine.

      1, when the value is not zero or blank,

       

      2, when the value is zero or blank,

       

      Regards,

      Community Support Team _Tang

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

        I cant provide the pbix as it contains sensitive data.. but its strange if I return the maxpressure value I get it as Blank. but If I try to return it with an IF condition I get error as multiple value supplied. Is it because there are multiple Blank values??