Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
tonylitvak
New Member

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

View solution in original post

5 REPLIES 5
MarcelBeug
Community Champion
Community Champion

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

group by filename.png

 

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"

 

Specializing in Power Query Formula Language (M)

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

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

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

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.