Calculated Column based on filtered values of inside table

[tonylitvak]

Hello everyone and thank you for your help in advance.  This is my first post in this forum!

I am trying to auto-calculate a new column called SuperCode.  For this example, i've specified wwhat the supercode column should be set. 

The rule: for each fileName, set the supercode to Success is theres a Status Code with a Success value for all instances of the records wiht the same FileName value.

Using this example, since file A doesn't have any success status code, the supercode will be set to Failed.

File B on the other hand has 1 Success Status Code, so it's supercode should be set to Success.

File C SuperCode should be set to Success since it only has 1 record and it's Status Value is Success. 

and Finally, File D SuperCode will be set to Success since there are 2 records and one of which includes a Success Status code.  

Thank you,

JobId	FileName	StartTime	Status	SuperCode
Job 1	File A	9/22/2017 12:06	Failed	Failed
Job 2	File A	9/22/2017 12:06	Failed	Failed
Job 3	File A	9/22/2017 12:06	Failed	Failed
Job 4	File B	9/21/2017 20:19	Failed	Success
Job 18	File B	9/21/2017 20:19	Failed	Success
Job 19	File B	9/21/2017 20:19	Success	Success
Job 20	File C	9/21/2017 20:19	Success	Success
Job 21	File D	9/21/2017 20:19	Failed	Success
Job 22	File D	9/21/2017 20:19	Success	Success

[MarcelBeug]

You didn't specifify if you are looking for a DAX or Power Query (M) solution.

With Power Query you can group on FileName with operations maximum of "Status" and "All Rows".

I adjusted the generated code and replaced "type table" by "Value.Type(Source)"

let
    Source = Table1,
    #"Grouped Rows" = Table.Group(Source, {"FileName"}, {{"SuperCode", each List.Max([Status]), type text}, {"AllRows", each _, Value.Type(Source)}}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"JobId", "StartTime", "Status"}, {"JobId", "StartTime", "Status"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded AllRows",Table.ColumnNames(Source)&{"SuperCode"})
in
    #"Reordered Columns"

[tonylitvak]

Marcel, thank you very much.  Im trying to keep this in the same table and looking for a DAX calculated column/measure.

[Zubair_Muhammad · Accepted solution]

Hi @tonylitvak

Add this calculated Column

SuperCode =
IF (
    CALCULATE (
        COUNT ( Table1[FileName] ),
        FILTER (
            ALL ( Table1 ),
            Table1[FileName] = EARLIER ( Table1[FileName] )
                && Table1[Status] = "Success"
        )
    )
        > 0,
    "Success",
    "Failed"
)

[tonylitvak]

Zubair, thank you for the excellent response.  It works!  I'll try to make sense of the functions which is taking me a bit.

[tonylitvak]

Ahhh. Yes, Im looking for a DAX calculated column/measure.