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

     

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