cancel
Showing results for
Search instead for
Did you mean:
Frequent Visitor

## Problem MAXX function with string

Hi,

I am currently working on a measure using the following table:

 Number Item Reason Code Status Center 60607788 abc 0B 10 G1A30 60607788 abc 0B 20 G1G30 60607788 abc 0B 30 G1C30 60607788 abc 0B 40 G1T40 60607788 abc 0B 50 G1X50 60607788 abc 0B 60 G1S20 60607788 abc 0B 70 G1R40 60607788 abc 0B 80 G1L30 60607788 abc 0B 90 G1H40 60607789 bbc 0B 10 G1A30 60607789 bbc 0B 20 G1G30 60607789 bbc 0B 30 G1C30 60607789 bbc 0B 40 G1T40 60607789 bbc 0B 50 G1X50 60607789 bbc 0B 60 G1S20 60607789 bbc 70 G1R40 60607789 bbc 80 G1L30 60607789 bbc 90 G1H40

I am using the following measure:

``````Last CC =

VAR MaxStatus = MAX('Work Order Time Transactions'[Status])
Return
MAXX(
FILTER(
'Work Order Time Transactions',
MaxStatus
&& 'Work Order Time Transactions'[Reason Code] = "0B"
),
'Work Order Time Transactions'[Center]
)``````

The measure works but does not give me the desired result. For the table above I want to know the last Center for maximum Status and reason Code 0B. In the table, the results for abc should be G1H40 since the maximum Status with reason Code 0B is 90, but the measure computes G1X50 since the string is the biggest. I found the gap in my measure but I am struggling to solve it.

I gladly appreciate any hints to solve this issue.

Thanks & Best regards

Hansson

1 ACCEPTED SOLUTION
Frequent Visitor

Hi Amit,

thanks a lot,  it works 🙂

Best regards

Arne

5 REPLIES 5
Helper II

MAXCC =
VAR maxStatus =
CALCULATE (
MAX ( 'Work Order Time Transactions'[Status] ),
ALLEXCEPT (
'Work Order Time Transactions',
'Work Order Time Transactions'[Item]
),
'Work Order Time Transactions'[Reason Code] = "0B"
)
RETURN
CALCULATE (
SELECTEDVALUE ( 'Work Order Time Transactions'[Center] ),
'Work Order Time Transactions'[Status] = maxStatus
)

Frequent Visitor

Hi Colacan,

thanks for your approach. I had to modify it though, with the Allexcept it did not work. However once I removed that filter it showed the same results.

Best regards

Hansson

Helper II

@hanssonnor  Hi Hanssonnor, I have noticed that extracting highest or lowest recode within categories is quite tipical problem which lots of people are facing. And Allexcept function is quite useful for those problems.

Cheers!

Super User IV

@hanssonnor , Try a measure like

calculate(lastnonblankvalue('Work Order Time Transactions'[Status], max('Work Order Time Transactions'[Center])),'Work Order Time Transactions'[Reason Code] = "0B")

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Frequent Visitor

Hi Amit,

thanks a lot,  it works 🙂

Best regards

Arne

## Helpful resources

Announcements

#### Happy New Year from Power BI

This is a must watch for a message from Power BI!

#### Check it Out!

Click here to read more about the December 2020 Updates!

#### Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

#### Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors