Forum Discussion
Convert DAX to M code
Hi, I have this DAX
VAR _action_id =
CALCULATE ( SELECTEDVALUE ( 'base fact_ds_action'[action_id] ) )
VAR _insertion_date =
CALCULATE ( SELECTEDVALUE ( 'base fact_ds_action'[insertion_date] ) )
VAR _ds_action_sk_finish =
CALCULATE (
MIN ( 'base fact_ds_action'[insertion_date] ),
FILTER (
ALL ( 'base fact_ds_action' ),
'base fact_ds_action'[action_id] = _action_id
&& 'base fact_ds_action'[insertion_date] > _insertion_date
)
)
RETURN
IF ( ISBLANK ( _ds_action_sk_finish ), TODAY (), _ds_action_sk_finish - 1 )
What would be the best approach to do the same logic on M code?
Please see attached CSV with Expected result
https://drive.google.com/file/d/16K8CjMfNRv1CXpMcZZQ5HKtA_dw6G4c4/view?usp=sharing
Logic:
There are repeated ID's with different dates. I need to retrieve the latest date. If the Date is the latest, then I need to have TODAY()
For example, ID = 1200
It appears in two rows. In the first one, 3/1/2022, the expected result is 8/1/2022, because there is another row with ID 1200 and latest date. On the other hand, the row with ID = 1200 and Date = 8/1/2022 has TODAY(), because there is no other newest date for that ID.
Thanks!
Hi milomilo2020 ,
Accoding to my understanding, you want to set Today for the row with the max date of each ID, the remains set the max date.
Please create a new blank query and paste the following M to the Advanced Editor dialog:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMFDSUTIyMDLSN9Q3VIrVAYoZIYsZQ8RMTDHVoeg1waLXAoteiDpLIECIGWERM8MiZmgAFrQAAlTBWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Date = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Date", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Count", each _, type table [ID=nullable number, Date=nullable date]}, {"Max", each List.Max([Date]), type nullable date}}), #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Date"}, {"Date"}), #"Added Custom" = Table.AddColumn(#"Expanded Count", "Custom", each if [Date]=[Max] then Date.From( DateTime.LocalNow()) else [Max]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Max"}) in #"Removed Columns"Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
Hi milomilo2020 ,
Accoding to my understanding, you want to set Today for the row with the max date of each ID, the remains set the max date.
Please create a new blank query and paste the following M to the Advanced Editor dialog:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMFDSUTIyMDLSN9Q3VIrVAYoZIYsZQ8RMTDHVoeg1waLXAoteiDpLIECIGWERM8MiZmgAFrQAAlTBWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Date = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Date", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Count", each _, type table [ID=nullable number, Date=nullable date]}, {"Max", each List.Max([Date]), type nullable date}}), #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Date"}, {"Date"}), #"Added Custom" = Table.AddColumn(#"Expanded Count", "Custom", each if [Date]=[Max] then Date.From( DateTime.LocalNow()) else [Max]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Max"}) in #"Removed Columns"Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.