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 

“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??

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

    Hi PowerrrBrrr 

    It seems MaxPressureonLatestDate is not in the measure I provided. Do you use the measure I provide wholly?

    Or kindly check the data in your file, is there any difference exists between the data in your test file and the sample data you shared?

     

     

    Regards,

    Community Support Team _Tang

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

      Its there with the solution Jose provided, in your case its "

      _maxValue

      " both give the same result and same error

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

        Hi PowerrrBrrr 

        Strangely. I am not able to repro the error you mention with the sample data you post.  

         

         

        Regards,

        Community Support Team _Tang