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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

April Fabric Community Update

Fabric Community Update - April 2024

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