Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I was trying to calculate the duration in days with two criteria. The goal is to get the days with these conditions below.
Just not sure if there is a way to calculate this in power query inside a custom column.
1 . IF the status is "Closed" calculate the Duration.Days(Duration.From( [ ClosedDateTime ] - [ CreatedDateTime ] ).
2. IF the status is "New/Active" calculate the Date.From(DateTime.LocalNow()) - [CreatedDateTime]
ClosedDateTime | CreatedDateTime | New Status | Results |
2021-04-04T07:11:15.9730000 | 2021-04-01T01:38:59.8970000 | Closed | |
2021-05-05T20:57:07.9330000 | 2021-05-05T20:35:26.8800000 | Closed | |
2021-05-12T11:52:35.0430000 | 2021-05-12T11:35:18.1670000 | Closed | |
1/1/1900 12:00:00 AM | 2021-03-31T19:17:28.5370000 | New / Active | |
1/1/1900 12:00:00 AM | 2021-05-04T16:54:19.2300000 | New / Active | |
1/1/1900 12:00:00 AM | 2021-05-08T15:59:02.9330000 | New / Active |
Solved! Go to Solution.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZC9DsIwDIRfJepcgs+pm8RbxQxTNtQJOiAhMYDg9TGC8icQEvZtZ3863XJZMTEm1JgKRQUU4nMMZFPVDxeFoCGpZJ9yvLmz7W4/rKu+HjFiKkwqUSn6HF4xoxtEufUp0XcMuFgUYbv11Lxjrq5hkDzaD2kwtc1EDqxEJtfN7/9hElCQFVE5eQnj/2I4uanrVofNcfhNkUtjaFUaRfYc6F9KKhCrVYmfCnul9Gc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ClosedDateTime = _t, CreatedDateTime = _t, #"New Status" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ClosedDateTime", type datetime}, {"CreatedDateTime", type datetime}, {"New Status", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Result", each Record.FieldOrDefault([Closed = Duration.Days([ClosedDateTime]-[CreatedDateTime]), #"New / Active" = Duration.Days(DateTime.LocalNow()-[CreatedDateTime])], [New Status], "other"))
in
#"Added Custom"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZC9DsIwDIRfJepcgs+pm8RbxQxTNtQJOiAhMYDg9TGC8icQEvZtZ3863XJZMTEm1JgKRQUU4nMMZFPVDxeFoCGpZJ9yvLmz7W4/rKu+HjFiKkwqUSn6HF4xoxtEufUp0XcMuFgUYbv11Lxjrq5hkDzaD2kwtc1EDqxEJtfN7/9hElCQFVE5eQnj/2I4uanrVofNcfhNkUtjaFUaRfYc6F9KKhCrVYmfCnul9Gc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ClosedDateTime = _t, CreatedDateTime = _t, #"New Status" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ClosedDateTime", type datetime}, {"CreatedDateTime", type datetime}, {"New Status", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Result", each Record.FieldOrDefault([Closed = Duration.Days([ClosedDateTime]-[CreatedDateTime]), #"New / Active" = Duration.Days(DateTime.LocalNow()-[CreatedDateTime])], [New Status], "other"))
in
#"Added Custom"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@CNENFRNL Thank you. I was trying to digest the last part on how it will be written in M. Is this something you can show , please?
As there's no SWITCH() statement in M language, Record.FieldOrDefault() is an alternative.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |