Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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,
JobId | FileName | StartTime | Status | SuperCode |
Job 1 | File A | 9/22/2017 12:06 | Failed | Failed |
Job 2 | File A | 9/22/2017 12:06 | Failed | Failed |
Job 3 | File A | 9/22/2017 12:06 | Failed | Failed |
Job 4 | File B | 9/21/2017 20:19 | Failed | Success |
Job 18 | File B | 9/21/2017 20:19 | Failed | Success |
Job 19 | File B | 9/21/2017 20:19 | Success | Success |
Job 20 | File C | 9/21/2017 20:19 | Success | Success |
Job 21 | File D | 9/21/2017 20:19 | Failed | Success |
Job 22 | File D | 9/21/2017 20:19 | Success | Success |
Solved! Go to 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" )
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"
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
142 | |
77 | |
63 | |
51 | |
47 |
User | Count |
---|---|
214 | |
84 | |
61 | |
61 | |
60 |