Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
Considering that I have a table as per below picture, with case number and count of status of each phase of a process.
I want to check each column and bring the name of the status to a "Latest Status Column" (in yellow in my picture).
Whar formula sould I use in Power Query to do that?
Solved! Go to Solution.
@mglomb it is definitely possible if you have Date as values for Recived, Fixed, Delivered
Measure =
VAR _f1 =
MAX ( 'Table'[Fixed] )
VAR _f2 =
MAX ( 'Table'[Received] )
VAR _f3 =
MAX ( 'Table'[Delivered] )
VAR _f4 =
MAX ( MAX ( _f1, _f2 ), _f3 )
VAR _f6 =
MAX ( 'Table'[Case] )
VAR _t =
UNION (
ADDCOLUMNS (
SELECTCOLUMNS ( 'Table', "Case", 'Table'[Case], "Date", 'Table'[Fixed] ),
"Attribute", "Fixed"
),
ADDCOLUMNS (
SELECTCOLUMNS ( 'Table', "Case", 'Table'[Case], "Date", 'Table'[Delivered] ),
"Attribute", "Delivered"
),
ADDCOLUMNS (
SELECTCOLUMNS ( 'Table', "Case", 'Table'[Case], "Date", 'Table'[Received] ),
"Attribute", "Received"
)
)
RETURN
MAXX ( FILTER ( _t, [Case] = _f6 && [Date] = _f4 ), [Attribute] )
pbix is attached
@mglomb it is definitely possible if you have Date as values for Recived, Fixed, Delivered
Measure =
VAR _f1 =
MAX ( 'Table'[Fixed] )
VAR _f2 =
MAX ( 'Table'[Received] )
VAR _f3 =
MAX ( 'Table'[Delivered] )
VAR _f4 =
MAX ( MAX ( _f1, _f2 ), _f3 )
VAR _f6 =
MAX ( 'Table'[Case] )
VAR _t =
UNION (
ADDCOLUMNS (
SELECTCOLUMNS ( 'Table', "Case", 'Table'[Case], "Date", 'Table'[Fixed] ),
"Attribute", "Fixed"
),
ADDCOLUMNS (
SELECTCOLUMNS ( 'Table', "Case", 'Table'[Case], "Date", 'Table'[Delivered] ),
"Attribute", "Delivered"
),
ADDCOLUMNS (
SELECTCOLUMNS ( 'Table', "Case", 'Table'[Case], "Date", 'Table'[Received] ),
"Attribute", "Received"
)
)
RETURN
MAXX ( FILTER ( _t, [Case] = _f6 && [Date] = _f4 ), [Attribute] )
pbix is attached
You can add a custom column like this.
= Table.AddColumn(#"Changed Type", "Actual Status", each if [Delievered] = 1 then "Delivered" else
if [Fix] = 1 then "Fixed" else
if [Received] = 1 then "Received" else "Other")
You can do this with the conditional column tool as well.
Hi @mglomb ,
You can use this formula in PowerQuery:
Actual status =
if [Delivered] = 1 then "Delivered" else
if [Fix] = 1 then "Fixed" else
if [Received] = 1 then "Received" else null
If I answered your question, please mark it as a solution to help other members find it more quickly.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
84 | |
72 | |
68 | |
41 | |
35 |
User | Count |
---|---|
109 | |
56 | |
52 | |
48 | |
42 |