Forum Discussion

tonylitvak's avatar
tonylitvak
New Member
8 years ago
Solved

Calculated Column based on filtered values of inside table

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 t...
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    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"
)
MarcelBeug's avatar
MarcelBeug
Community Champion
8 years ago

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's avatar
tonylitvak
New Member
8 years ago

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