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

Problem MAXX function with string

Hi,

 

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

 

NumberItemReason CodeStatusCenter
60607788abc0B10G1A30
60607788abc0B20G1G30
60607788abc0B30G1C30
60607788abc0B40G1T40
60607788abc0B50G1X50
60607788abc0B60G1S20
60607788abc0B70G1R40
60607788abc0B80G1L30
60607788abc0B90G1H40
60607789bbc0B10G1A30
60607789bbc0B20G1G30
60607789bbc0B30G1C30
60607789bbc0B40G1T40
60607789bbc0B50G1X50
60607789bbc0B60G1S20
60607789bbc 70G1R40
60607789bbc 80G1L30
60607789bbc 90G1H40

 

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

Hi Amit,

 

thanks a lot,  it works 🙂

 

Best regards

Arne

 

View solution in original post

5 REPLIES 5
Helper II
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
    )

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

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

Hi Amit,

 

thanks a lot,  it works 🙂

 

Best regards

Arne

 

View solution in original post

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

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

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors