Calculated column based on multiple rows and checking results.

[prj102]

I'm looking for a calculated column function that will help me determine an overall status based on multiple rows of data.  

My data looks something like this and I am looking to create the "Calculated Overall Status" column:

Name	Type	Status	Calculated Overall Status
PT001	g	Failed	Failed
PT001	s	Passed	Failed
PT002	g	Passed	Passed
PT002	s	Passed	Passed
PT003	g	Passed	Warning
PT003	s	Warning	Warning

If a "Name" shows up more than once in the data, I want to look at all the statuses and mark them as "Passed" if they all passed, "Falied" if any of the statuses failed and Warning if there is a warning in any status.

If I'm not thinking of this correctly please send me in the right direction

[TomMartens]

Hey,

try this DAX statement:

Column = 
var _name = 'Table'[Name]
var theString = CONCATENATEX(FILTER(ALL('Table') , 'Table'[Name] = _name) , 'Table'[Status] , "|" , 'Table'[Status] , ASC)
return
SWITCH(
    TRUE()
    , PATHCONTAINS(theString , "Failed") , "Failed"
    , PATHCONTAINS(theString , "Warning") , "Warning"
    , "Passed"
)

This calculated column looks like your expected result:

What the DAX does is this:

1. it stores the current value of the column Name to the variable _name
2. it creates a string of all the status values for one Name separated by "|", this allows interpreting the string as path
3. using SWITCH to check the occurence of Failed or Warning inside the "Path"

Just switch the order of the rows

PATHCONTAINS( ... , "Failed") , "Failed"

PATHCONTAINS( ... , "WArning") , "Warning"

if "Warning" tops "Failed".

Hopefully this is what you are looking for.

Regards,

Tom

[prj102]

I see that your solution is working on the sample data and I even recreated it working myself but its not working on our production data.  Can't explain why at this point...  In the real data, every row is reporting Passed so I can only assume its defaulting to that.  More to come 

[v-chuncz-msft · Accepted solution]

@prj102 

You may try the DAX below.

Column =
SWITCH (
    TRUE (),
    ISEMPTY (
        FILTER (
            'Table',
            'Table'[Name] = EARLIER ( 'Table'[Name] )
                && 'Table'[Status] <> "Passed"
        )
    ), "Passed",
    NOT (
        ISEMPTY (
            FILTER (
                'Table',
                'Table'[Name] = EARLIER ( 'Table'[Name] )
                    && 'Table'[Status] = "Failed"
            )
        )
    ), "Failed",
    NOT (
        ISEMPTY (
            FILTER (
                'Table',
                'Table'[Name] = EARLIER ( 'Table'[Name] )
                    && 'Table'[Status] = "Warning"
            )
        )
    ), "Warning"
)