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 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,

 

JobIdFileNameStartTimeStatusSuperCode
Job 1File A9/22/2017 12:06FailedFailed
Job 2File A9/22/2017 12:06FailedFailed
Job 3File A9/22/2017 12:06FailedFailed
Job 4File B9/21/2017 20:19FailedSuccess
Job 18File B9/21/2017 20:19FailedSuccess
Job 19File B9/21/2017 20:19SuccessSuccess
Job 20File C9/21/2017 20:19SuccessSuccess
Job 21File D9/21/2017 20:19FailedSuccess
Job 22File D9/21/2017 20:19SuccessSuccess
  • 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"
)

5 Replies

  • MarcelBeug's avatar
    MarcelBeug
    Icon for Community Champion rankCommunity 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.

    • tonylitvak's avatar
      tonylitvak
      New Member
      8 years ago

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

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Icon for Community Champion rankCommunity Champion
        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"
)