Forum Discussion
Helper ICustom column M language filter formula translation from desktop new column formula
Hi Vauban ,
You are using DAX instead of power query. If you want to achieve this in power query, you could refer to below code
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIEYVOlWB0E18gAzjUCCRmBuUkwxSg8UzgPpNQChWcG5xmDMTIPaGYsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [name = _t, id = _t, amount = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}, {"id", Int64.Type}, {"amount", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"name", "id"}, {{"mivx", each List.Min([amount]), type number}, {"all", each _, type table [name=text, id=number, amount=number]}}), #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"amount"}, {"amount"}) in #"Expanded all"If you want to use your expression, you need to create calculated column instead of power query, you need to create like below
In addition, you also could use measure to achieve this, which will show in table visual, you could try below expression
Measure 2 = CALCULATE(MIN(t[amount]), ALLEXCEPT(t,t[name],t[id]))Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
M is a completely different language than DAX, so you can never reuse any formulas. You can find the M reference here:
https://docs.microsoft.com/en-us/powerquery-m/
Sometimes, like in this case when you want to find other rows from the same table to calcualte something, DAX could be a lot easier than M. If it works, why don't use it? Compression of the data might be less optimal when using calculated columns in DAX, but if you don't have huge amounts of data it probably doesn't matter.
Best Regards // Ulf
Helper IThank you Anonymous.
I played with the Custom Column in Query Editor some more.
Because I didn't see how writing DAX into my dashboard application would work given that I have to go to Query Editor and do more calcs on whatever columns I bring in there.
So this is what I came up with for the Custom Column formula in Query Editor:
MinDatePerStatePerWorkItemId = minx(filter([Work Item Id] = earlier([Work Item Id]) & [State] = earlier([State])),[Date])
I got excited that there weren't any syntax errors but the not finding minx error is a problem of course.
I looked for what other formula names besides "minx" in this situation is there but couldn't see it.
Thank you for any continued thoughts on this.
Best regards
daxCommunity Support
Hi Vauban ,
You are using DAX instead of power query. If you want to achieve this in power query, you could refer to below code
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIEYVOlWB0E18gAzjUCCRmBuUkwxSg8UzgPpNQChWcG5xmDMTIPaGYsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [name = _t, id = _t, amount = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}, {"id", Int64.Type}, {"amount", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"name", "id"}, {{"mivx", each List.Min([amount]), type number}, {"all", each _, type table [name=text, id=number, amount=number]}}), #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"amount"}, {"amount"}) in #"Expanded all"If you want to use your expression, you need to create calculated column instead of power query, you need to create like below
In addition, you also could use measure to achieve this, which will show in table visual, you could try below expression
Measure 2 = CALCULATE(MIN(t[amount]), ALLEXCEPT(t,t[name],t[id]))Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.