Forum Discussion

PowerrrBrrr's avatar
PowerrrBrrr
Helper 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
            2                        MachineB
            3                        MachineC
            4                        MachineD

 

Table B

ID          timeseriesId             Description
1            34333             Pressure rate
1            34339             Flow rate
1             44343             Temperature
2             22211            Pressure rate
2            33222            Flow rate

 

Table C

 

TimeseriesId                      Value                      Timestamp
            34333                       34                 2021-12-12 12:09:12
            34333                                        2021-12-21 10:00:00
            34333                       12                 2021-01-10 21:09:12
            44343                       19                 2021-12-12 12:09:12
           22211                       0                 2021-12-21 10:00:00
           22211                       91                 2021-11-11 12:09:12

 

So if you see here 2021-12-21 is the latest date here and pressure rate has not been recorded for any of the machine. I need the Name as "Blank" (since no pressure has been recorded for any machine yet for latest day) in the card which is about Maximum Pressure lately. 

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

13 Replies

  • YukiK's avatar
    YukiK
    Impactful Individual
    4 years ago

    This should do it!

     

    Consider giving it a thumbs up and accept as a solution if this helped!

  • Jos_Woolley's avatar
    Jos_Woolley
    Solution Sage
    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
      Helper III
      4 years ago

      Jos_Woolley Your solution works fine but fails when the pressure is blank on latest day. Like if the latest there is no pressure recorded on any machine on latest day, it should give me blank or not recorded instead of machine name but what I get is error as "A table of Multiple values were supplied while it was expected a single value". how can i handle this

  • Jos_Woolley's avatar
    Jos_Woolley
    Solution 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
      Helper 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
        Community 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

  • v-xiaotang's avatar
    v-xiaotang
    Community 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.