Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |