Lookup max value in an appended table

[schristian]

I'm trying to figure out a way to lookup a max value from the BP column to match with a name (Comm ID). When I do lookup value, I just get to notification that there are multiple values which is an issue I anticipated.

Ultimately, what I would want is for any row with A5 as the Comm ID to have 3 as a max value while any row with B6 as the Comm ID to have 4 as its Max value.

Is there any way to pull this off?

BP	Comm ID	Max Value
1	A5
2	A5
3	A5
1	B6
4	B6

[v-jayw-msft]

Hi @schristian ,

If i understand you correctly, ALLEXCEPT() function might be helpful for you.

You can use the formula below both in Measure and Calculated Column.

Max Value = CALCULATE(MAX('Table'[BP]),ALLEXCEPT('Table','Table'[Comm ID]))

Result would be shown as below.

 

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[schristian]

hi all - thanks for everyones suggestions. I think I should've included that sometimes the BP column is blank. All of the suggestions returned a 0 value. Could the blanks be a cause?

[v-jayw-msft]

Hi @schristian ,

Based on my research, I don't think the blank value could affect the result of my formula.

It would be better to share your pbix or sample data to us if you don't have any Confidential Information. In that case we might help you find out what caused the results return 0 values.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

You are welcome.  Blanks should not be the reason for my formula not working.  See the screenshot below.

[schristian]

@Ashish_Mathur Noticed I made an error elsewhere and your solution worked. Appreciate everyone's help with this!

[Anonymous]

HI @schristian ,

May be you can give a try with this:

Max Value =

Var BP= SELECTEDVALUE('Table'[BP])

Var MaxValue=

if(BP<>blank() || BP= BLANK(),

Calculate(Max('Table'[BP]),ALLSELECTED('Table'[BP])))

return

MaxValue

My output:

-Tejaswi

[Ashish_Mathur · Accepted solution]

Hi,

Try this calculated column formula

=CALCULATE(MAX(DATA[BP]),FILTER(DATA,DATA[COMM ID]=EARLIER(DATA[COMM ID])))

Hope this helps.

[Anonymous]

HI @schristian ,

You may go thru this solution for the lookup value error.

https://community.powerbi.com/t5/Desktop/LOOKUPVALUE-quot-A-table-of-multiple-values-was-supplied-wh...

Or you can try this measure for your issue:

Max Value = Calculate(Max('Table'[BP]),ALLSELECTED('Table'[BP]))

May be this helps.

Thanks,

Tejaswi