I am trying to test 20 conditions on a table with different (tests such as [value] should not be less than 0, [rate] should not be more than 1, date should not be older than 2024 etc.). Is there an easy way to create an if statement that can test all these conditions and return a statement like "Condition 1 met, Condition 5 met, Condition 7 met" etc.

Multiple if statement and concatenating a result.

5 Replies

Community Champion

Hi AJ2960710, check this:

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc3JCQAxDAPAXvyOQT4S0ktI/22sDwj78WOE5HNIaBBGXoUaYzIW3XFoJjX7jwVVsE6SGVIJ7zBvRyCLlnsVVF9DGV6JVeL2vsR/660VttqFsWvrfg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [value = _t, rate = _t, date = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"value", Int64.Type}, {"rate", type number}, {"date", type date}}, "sk-SK"),
    Ad_ConditionsCheck = Table.AddColumn(ChangedType, "Check", each
        [ conditions = { [value] >= 0, [rate] <= 1, [date] >= #date(2024,1,1) },
          b = List.Transform(List.Positions(conditions), (x)=> "Condition " & Text.From(x+1) & " " & {"not met", "met"}{Byte.From(conditions{x})}),
          c = Text.Combine(b, " | ")
        ][c], type text)
in
    Ad_ConditionsCheck

AJ2960710

Frequent Visitor

1 year ago

dufoq3 this looks superb! is there an easy way so I can make the text more dynamic, to say "Value<0 , rate>1" etc.? I feel the condition text is lightweight, but would be helpful to have the actual name of the condition. If not this looks great!Thanks!

dufoq3

Community Champion

1 year ago

It is possible, but useless in my opinion:

Define conditions in Conditions step this way:

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc3JCQAxDAPAXvyOQT4S0ktI/22sDwj78WOE5HNIaBBGXoUaYzIW3XFoJjX7jwVVsE6SGVIJ7zBvRyCLlnsVVF9DGV6JVeL2vsR/660VttqFsWvrfg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [value = _t, rate = _t, date = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"value", Int64.Type}, {"rate", type number}, {"date", type date}}, "sk-SK"),
    Conditions = [ value = ">= 0",
    rate = "<= 1",
    date = ">= #date(2024,1,1)"
  ],
    StepBack = ChangedType,
    Ad_Check = Table.AddColumn(StepBack, "Check", each 
        [ curCond = "value >= 0",
          a = Expression.Evaluate(Text.Replace("value >= 0", "value", Text.From(Record.Field(_, "value")))),
          b = List.Transform(Record.FieldNames(Conditions), (x)=> 
                    [ format1 = (f)=> if f is number then Text.Replace(Text.From(f), ",", ".") else if f is date then "#date(" & Date.ToText(f, [Format="yyyy,MM,dd"]) & ")" else Text.From(f),
                      format2 = (f)=> if Text.Contains(f, "#date") then f else Text.Replace(f, ",", "."),
                      b1 = format1(Record.FieldOrDefault(_, x, null)),
                      b2 = format2(Record.Field(Conditions, x)),
                      b3 = Expression.Evaluate(b1 & b2),
                      b4 = x & " " & b2 & " is " & Text.Upper(Text.From(b3))
                    ][b4] ),
          c = Text.Combine(b, "#(lf)")
        ][c], type text)
in
    Ad_Check

ZhangKun
Super User
1 year ago
Whether you use DAX or M language, the method is similar to the formula in the figure below:

First compare each condition, and then select the largest (or smallest) row (or value) in the filtered results according to the priority.

jgeddes

Super User

1 year ago

Here is another approach you could try.
If you have a table that looks like this...

and you have some conditions to test...
Value < 10
Rate > 1

Year >= 2020

Rate = 2.5

and you want an end result of...

The following sample code will get this result.
Table

let
    Source = 
    Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQ0lEy0jMFkQZGhkqxOiAxkKAZVMwIImYKEjNHETMCixlDxYyVYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Value = _t, Rate = _t, Year = _t]),
    #"Changed Type" = 
    Table.TransformColumnTypes(Source,{{"Value", Int64.Type}, {"Rate", type number}, {"Year", Int64.Type}}),
    Custom1 = 
    Table.AddColumn(#"Changed Type", "Condition Tests", each fxFieldTests(_), type text)
in
    Custom1

Function fxFieldTests

(recordInput as record) as text =>
let
    //recordInput = [Value=10, Rate=2.5, Year=2021],    //debugging use only
    testList = 
    {
        Record.Field(recordInput, "Value") < 10,
        Record.Field(recordInput, "Rate") > 1,
        Record.Field(recordInput, "Year") >= 2020,
        Record.Field(recordInput, "Rate") = 2.5
    },
    truePositions =
    Text.Combine(
        List.Transform(
            List.PositionOf(
                testList, 
                true, 
                Occurrence.All
            ),
            each "Condition " & Number.ToText(_+1) & " met"
        ),
        ", "
    )
in
    truePositions

You can see the format of the conditions in the testList step of the function query.
Hope this helps.

Forum Discussion

Multiple if statement and concatenating a result.

5 Replies

Related content

Multiple IF statements in DAX

Multiple nested IF statements

Custom Column IF Statement with Concatenation

Custom Column: Concatenate Text with multiple IF statements

Concatenate Expression with If Statement (Report Builder)