Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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.
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.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/td-p/985814
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
User | Count |
---|---|
102 | |
84 | |
77 | |
70 | |
67 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |