CODEWITHSUNDEEP
excelpowerbidaxpowerpivot
Aaron
Aaron

Reputation: 331

MAX Value based on two Numbers and one Text

I have a huge Order-Master-Table that contains the complete manufacturing routing for each order. Now I would like to determine the last tracked quantity in this table. The table has a structure like:

The measure should look at Prio 1 at first, then at Prio 2, and at last on Prio 3. Prio and Prio 2 are Numbers between 2 and 99 possible. In Prio 3 are different Text strings possible, but for the tracked quantity are just "OX" and "OK" necessary. Here has "OX" always more priority than "OK".

How would you build that as a DAX Measure? MAXX, CALCULATEDTABLE, RANK, TOPN?

This is my first approach:

VAR Max1 =
    MAXX (
        ALLSELECTED ( 'Order Master' ),
        'Order Master'[Prio 1]
    )
VAR Max2 =
    MAXX (
        ALLSELECTED ( 'Order Master' ),
        'Order Master'[Prio 2]
    )
RETURN  
MAXX(
    FILTER(
        'Order Master',
            'Order Master'[Prio 1] = Max1 && 'Order Master'Prio 2] = Max2
    ),
    'Order Master'[Quantity]
)

Upvotes: 0

Views: 300

Answers (1)

Dominique
Dominique

Reputation: 17493

I would translate "OX" and "OK" into numbers, just like the other priorities. This can be done very simply using an IF-formula:

=IF(A1="OX";0;IF(A1="OK";1;-1))

(Just as an example, obviously)

If, however, by "OX" you mean every possible value for "X", like "O1", "O32", ..., then I'd propose you to write a VBA function for performing the mentioned translation.

Upvotes: 2

Related Questions