Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends September 15. Request your voucher.

Reply
PBInewuser
Microsoft Employee
Microsoft Employee

Conditional Column on Hierarchy

Hi there,

Please see the sample table below

When Status is "Failed" for any Contract No or Contract the outcome on New Column Should be "Failed"

Please advise a measure

 

Table.PNG

 

Thanks,

 

1 ACCEPTED SOLUTION
edhans
Super User
Super User

Hi @PBInewuser , 

 

You are using a few terms interchangably that are not the same thing.

 

A conditional column is a column in Power Query. I would generally recommend this is the way to go as you are changing the model with a new column, but if I am undersanding your question, you want it for any single contract number that has any status as failed, so Power Query isn't the way to go.

 

You are then asking for a new column, but then ask for a measure. I think you want a new column in a visual, not the model, and a measure is the way to go.

 

Final Status = 
VAR varFailedStatusCount =
    CALCULATE(
        COUNTROWS('Table'),
        REMOVEFILTERS('Table'[Date],'Table'[Status]),
        'Table'[Status] = "Failed"
    )
var Result = 
    IF(
        varFailedStatusCount > 0,
        "Failed",
        "Success"
    )
RETURN
    Result

 

That returns this:

edhans_0-1595549196373.png

Contract 1 is failed, but contract 2 has no falures, so the final status is Success.

 

My PBIX is here if you want to look at it.

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

1 REPLY 1
edhans
Super User
Super User

Hi @PBInewuser , 

 

You are using a few terms interchangably that are not the same thing.

 

A conditional column is a column in Power Query. I would generally recommend this is the way to go as you are changing the model with a new column, but if I am undersanding your question, you want it for any single contract number that has any status as failed, so Power Query isn't the way to go.

 

You are then asking for a new column, but then ask for a measure. I think you want a new column in a visual, not the model, and a measure is the way to go.

 

Final Status = 
VAR varFailedStatusCount =
    CALCULATE(
        COUNTROWS('Table'),
        REMOVEFILTERS('Table'[Date],'Table'[Status]),
        'Table'[Status] = "Failed"
    )
var Result = 
    IF(
        varFailedStatusCount > 0,
        "Failed",
        "Success"
    )
RETURN
    Result

 

That returns this:

edhans_0-1595549196373.png

Contract 1 is failed, but contract 2 has no falures, so the final status is Success.

 

My PBIX is here if you want to look at it.

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.