DAX hELP

[pmadam]

I have a calculated measure.

_DATE CHECK = IF('Union Table'[AddedDate] = CALCULATE(MIN('Union Table'[AddedDate]),ALLEXCEPT('Union Table','Union Table'[OrderNumber])),"OLDEST DATE","NOT OLDEST DATE")

I am trying to create a custom column in Power Query editor and Filter out the data "NOT OLDEST DATE" but Im not not able create a custom colunmn. Please help

[Anonymous · Accepted solution]

Hi  @pmadam ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XdDLCcQwDATQXnIORKOPP1VsASG3rWD7P6wjg2x0MTY8RmPd9/H5fQnHediFi4l5XFH0PbWM13PuRILUNokikbJS7D2ZRBOpQTBTpJcg7KQFkeLEYIn0ICoOzVaKpLpGDiGUyFa3z3E9p4DXJO8r2Baj06xRgBuyGsamWf/ubpRqNkxhmvexxmPDzx8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [OrderNumber = _t, AddedDate = _t, Qty = _t, Sales = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"OrderNumber", type text}, {"AddedDate", type date}, {"Qty", Int64.Type}, {"Sales", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"OrderNumber"}, {{"Mindate", each List.Min([AddedDate]), type nullable date}, {"Details", each _, type table [OrderNumber=nullable text, AddedDate=nullable date, Qty=nullable number, Sales=nullable number]}}),
    #"Expanded Details" = Table.ExpandTableColumn(#"Grouped Rows", "Details", {"AddedDate", "Qty", "Sales"}, {"AddedDate", "Qty", "Sales"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Details", "_DATE CHECK", each if [AddedDate]=[Mindate] then "OLDEST DATE" else "NOT OLDEST DATE"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Mindate"})
in
    #"Removed Columns"

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[amitchandak]

@pmadam , This DAX formula and correct with you min at order number

_DATE CHECK = IF('Union Table'[AddedDate] = CALCULATE(MIN('Union Table'[AddedDate]),ALLEXCEPT('Union Table','Union Table'[OrderNumber])),"OLDEST DATE","NOT OLDEST DATE")

or

_DATE CHECK = IF('Union Table'[AddedDate] = MINX(filter('Union Table','Union Table'[OrderNumber] =earlier('Union Table'[OrderNumber])) 'Union Table'[AddedDate]),"OLDEST DATE","NOT OLDEST DATE")

or, if you need over all min

_DATE CHECK = IF('Union Table'[AddedDate] = Min('Union Table'[AddedDate]) ,"OLDEST DATE","NOT OLDEST DATE")

For overall min in power query, you can use List.Min

refer List.Min and List.MinN: https://youtu.be/9wYdttOPWaQ