Else if function displaying the max date

[Anonymous]

Hi there

I want to create a calculated column which includes text and also a max date. Below is what I want to do, but M Query doesn't like it because I can only see options for a List.Max which isn't what I want. 

Can I do this? 

= Table.AddColumn(Between, "Column", each if [Duration] >= 60 then "60 to "Max[Duration] else if [Duration] >= 30 then "30 to 59" else if [Duration] >= 11 then "11 to 29" else "0 to 10")

[MFelix · Accepted solution]

Hi @Anonymous ,

M language is based on the row levels to get the maximum of a column for example as you refer you need to pick up the List.Max.

Assuming that you want to pick up the maximum value of the duration column you need to do your column like:

= Table.AddColumn(Between, "Column", each if [Duration] >= 60 then "60 to " & 
Number.ToText (List.Max(#"PreviousStepName"[Duration])) else if [Duration] >= 30 then "30 to 59" else if [Duration] >= 11 then "11 to 29" else "0 to 10")

I'm assuming that your duration column is in number format that is why we need to use the Number.ToText.

Also using the List.Max you need to reference the step you want to calculate the maximum and the column thats the part 

List.Max(#"PreviousStepName"[Duration])

[Anonymous]

@MFelix well knock me down and call me Aunt Sally. This worked. Thank you! 

[ryan_mayu]

@Anonymous 

I think it will be better to use DAX to create the column.

Could you please the sample data and expected output?

[Anonymous]

I can't attach anything to here. Happy to do it in DAX, can't figure it out in there either!

The  [Duration] column is a numerical column containing any number ranging from 0 to 1000. At the moment, the highest value in there is 477. Tomorrow it will be different.

I want a new column which is of this logic:

If the [Duration] column is between 0 and 10, it says "0 to 10" 

If the [Duration] column is between 11 and 29 it says "11 to 29"

If the [Duration] column is between 30 and 59 it days "30 to 59"

If the [Duration] column is over 60 it says "60 to 477"

[MFelix]

@Anonymous ,

If you prefer DAX you can use:

Column =
SWITCH (
    TRUE (),
    'Table'[Duration] >= 60, "60 to "
        & FORMAT ( CALCULATE ( MAX ( 'Table'[Duration] ), ALL ( 'Table'[Duration] ) ), "#" ),
    'Table'[Duration] >= 30, "30 to 59",
    'Table'[Duration] >= 11; "11 to 29",
    "0 to 10"
)

[parry2k]

@Anonymous yes this can be done in PQ, I guess you want MAX across the whole data table?

[Anonymous]

Hi again @parry2k  yes indeed I do. 

[parry2k]

@Anonymous here is a sample script that you can apply in your table, start a blank query, click advanced editor and paste the code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Rcy5DQAwDALAXahT5HEez2J5/zWCQpHuhIAINBS0iiyBTnZxkEO0X5g/XaSJm1ziIY8/OulKudZFXg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"Value", Int64.Type}}),
    #"Group" = Table.Group(#"Changed Type", {}, {{"MaxValue", each List.Max([Value]), type number}}),
    #"Max Value" = #"Group"{0}[MaxValue],
    #"Add Column" = Table.AddColumn(#"Changed Type", "Range",each if [Value] >= 60 then "60 - " & Number.ToText(#"Max Value") else "0-60", type text)
in
    #"Add Column"

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡