Get the category with maximum value

[PowerrrBrrr]

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. 

[v-xiaotang]

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.

[Jos_Woolley]

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

Regards

[PowerrrBrrr]

@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]

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]

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]

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]

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]

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]

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

_maxValue

" both give the same result and same error

[v-xiaotang]

Hi @PowerrrBrrr 

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

Regards,

Community Support Team _Tang

[Jos_Woolley · Accepted solution]

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]

@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

[YukiK]

This should do it!

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