Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. 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 |
---|---|
72 | |
70 | |
57 | |
37 | |
36 |
User | Count |
---|---|
85 | |
65 | |
60 | |
46 | |
45 |