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
Ctq17922
New Member

Convert DAX to M Code Help!

i am trying to convert the following DAX to m code in power query. it looks at a row in the table and if the work orer column is the same number returns the last staqtus change date. help would be appreciated.

 

CALCULATE(

    MAX('Maximo Work Order Status'[Changed Date]),

        FILTER(

            ALLEXCEPT('Maximo Work Order Status', 'Maximo Work Order Status'[Work Order Number]),

            'Maximo Work Order Status'[Changed Date]<EARLIER('Maximo Work Order Status'[Changed Date])))

1 REPLY 1
sevenhills
Super User
Super User

Check this ... I used some random sample data to get the previous max date row.

 

Input data:

sevenhills_1-1695693897930.png

 

M code:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc7BDcAgDAPAXfJGcmxKW2ZB2X8NoI/2kfp5smSPYVyxYt7gJ+SSRXmVAuunWtmK48dasgtiQu+Q0tAN9oT701OtFjEB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order Number" = _t, #"Changed Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Changed Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Order Number"}, {{"c dates list", each _[Changed Date], type list}, {"aa", each _, type table [Order Number=nullable text, Changed Date=nullable text]}}),
    #"Merged previous Dates Per Group" = Table.NestedJoin(#"Grouped Rows", {"Order Number"}, #"Changed Type", {"Order Number"}, "Previous Calendar Date", JoinKind.LeftOuter),
    #"Expanded Previous Calendar Date" = Table.ExpandTableColumn(#"Merged previous Dates Per Group", "Previous Calendar Date", {"Order Number", "Changed Date"}, {"Order Number.1", "Changed Date.1"}),
    AddPreviousOrder = Table.AddColumn (#"Expanded Previous Calendar Date", "Previous Order Date", each let bDate = [Changed Date.1] in List.Max(List.Select([c dates list], each _ < bDate)), type date),
    #"Removed Columns" = Table.RemoveColumns(AddPreviousOrder,{"c dates list", "aa", "Order Number.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Changed Date.1", "Changed Date"}}),
    #"Sorted Rows1" = Table.Sort(#"Renamed Columns",{{"Order Number", Order.Ascending}, {"Changed Date", Order.Ascending}})
in
    #"Sorted Rows1"

 

I added some steps for your clarity...

 

Output:

sevenhills_0-1695693877144.png

 

Hope this helps!

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

Top Solution Authors
Top Kudoed Authors