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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

Regards
Zubair

Please try my custom visuals

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

Regards
Zubair

Please try my custom visuals

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.