label group of rows based on first row value

[highxsky]

Hi all,

I am trying to label a group of rows based on the value of a column for the first row.

This is an order data table, that looks like this :

Order Number	Phase	Review Mode
123ABC	1	Manual review
123ABC	2	Automated review
123ABC	3	Automated review
123ABC	4	Automated review
456DEF	1	Automated review
456DEF	2	Automated review
456DEF	3	Manual review
456DEF	4	Manual review

So what I would like to do is label each Order Number, in a new calculated column, based on the review mode during phase 1 e.g.:

123ABC --> "Manual review"

456DEF --> "Automated Review"

From what I've seen, many people have similar issues but with numeric or date values and they tend to use CALCULATE and MAX/MIN for example.
In that case, I work with string values and I have several column like that that I'd like to evaluate, so I'd try to avoid replacing strings by numerical equivalents (i.e. "automated" = 0, "manual" = 1).

Would you have any suggestion/hint as how to deal with this ?

Thanks in advance,

Regards,

Pierre

[v-alq-msft · Accepted solution]

Hi, @highxsky 

Based on your description, you may create a calculated column or a measure as below.

Calculated column:
Result Column = 
var _ordernumber = 'Table'[Order Number]
return
CALCULATE(
    MAX('Table'[Review Mode]),
    FILTER(
        ALL('Table'),
        'Table'[Order Number] = _ordernumber&&
        'Table'[Phase] = 
        CALCULATE(
            MIN('Table'[Phase]),
            FILTER(
                ALL('Table'),
               'Table'[Order Number] = _ordernumber
            )
        )
        
    )
)

Measure:
Result Measure = 
var _ordernumber = SELECTEDVALUE('Table'[Order Number])
return
CALCULATE(
    MAX('Table'[Review Mode]),
    FILTER(
        ALL('Table'),
        'Table'[Order Number] = _ordernumber&&
        'Table'[Phase] = 
        CALCULATE(
            MIN('Table'[Phase]),
            FILTER(
                ALL('Table'),
               'Table'[Order Number] = _ordernumber
            )
        )
        
    )
)

Result:

Best Regards

Allan

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

[Greg_Deckler]

https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/td-p/985814

[highxsky]

Hi Greg,

thanks for the reply and the link.

In my case, I am trying to retrieve the value from the first row of item A and apply it to each row row of item A.

I have tried some manipulations with your model, but without getting the desired output.

First part of the query - with a slight FILTER twist - works well, I get the output I'm looking for.
Second part of the query is another story though.

Part 1, DaX studio, output OK

Part 2, Power Query, output KO

Could you please provide me further hints so I can solve that issue?

Thanks again,

Pierre