Forum Discussion

prj102's avatar
prj102
Helper I
6 years ago
Solved

Calculated column based on multiple rows and checking results.

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:

 

NameTypeStatusCalculated Overall Status
PT001gFailedFailed
PT001sPassedFailed
PT002gPassedPassed
PT002sPassedPassed
PT003gPassedWarning
PT003sWarningWarning

 

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

 

Need Help
  • v-chuncz-msft's avatar
    v-chuncz-msft
    6 years ago

    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"
)

     

3 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    6 years ago

    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's avatar
      prj102
      Helper I
      6 years ago

      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's avatar
        v-chuncz-msft
        Community Support
        6 years ago

        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"
)