Forum Discussion

Anonymous

Not applicable

4 years ago

Solved

Conditional If or Switch using var DAX

Hi All, Please help me with the below condition , Column - S.NO,ID,OPS,PP are my Table. Condition - Mentioned the required Condition Result need to come as - Result which i need ...

Need Help

Show and Tell

Anonymous

4 years ago

Hi Anonymous - I don't think this is possible using DAX, but it is possible using Power Query. Please try the following:

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY5BCsAgDAT/krPCRpPYvkX8/zcapYQW9BKWYWdJ78SUiEv1C+aMAmSYB2CHRupUwjg1ajTadfsVtcUl+OtPqB5U7LelawtbNh0L51hp3y9pjAc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [S.No = _t, ID = _t, OPS = _t, PP = _t]),
  #"Change Data Types" = Table.TransformColumnTypes(Source,{{"S.No", Int64.Type}, {"ID", Int64.Type}}),
  Buffer = Table.Buffer ( #"Change Data Types" ),
  #"Add Conditional Result" = Table.AddColumn(Buffer, "Condition Result", each 
    let 
      i=[S.No], x=[ID], OPS=[OPS], PP=[PP], t=Buffer,
      text1 = Text.Length(OPS), text2 = Text.Length(PP),
      test_1 = 
        if OPS = PP and not ( text1 = 0 or text2 = 0 ) 
        then "Match" 
        else null,
      test_2 = 
        if ( text1 = 0 or text2 = 0 ) 
        then 
          if
            List.ContainsAny( 
              List.Combine( 
                { 
                  Table.SelectRows( t , each [ID] = x and ( [S.No] = i - 1 or [S.No] = i + 1) )[OPS],
                  Table.SelectRows( t , each [ID] = x and ( [S.No] = i - 1 or [S.No] = i + 1) )[PP]
                } 
              ) ,
              { OPS , PP } 
            ) 
          then "Extra Population" 
          else null
        else null,
      test_3 = 
        if OPS <> PP 
        then "Non Match" 
        else null, 
      Result = List.First( List.RemoveNulls( { test_1, test_2, test_3, "Missed by ML" } ) )
    in 
      Result
  )
in
  #"Add Conditional Result"

Anonymous

Not applicable

4 years ago

Hi Daryl ,

Extra Population is not working , please find the below screen shot for your references

For unique image , the result should be in this format ,

Extra population should match like this format

But as per your condition it does Extra Population and missed match doesn't working as the coditional result , could you please recheck send me the code once again

Anonymous

Not applicable

4 years ago

Hi Anonymous , I have updated my approach to "" blank values. I think this get closer to your requirement. However I am stuck on your "...091.TIF" example. The "Extra Population" has identified the "071-1950-040-000" from the previous "No match". This is going to difficult to fix. But I hope my example has helped you understand what is possible using Power Query M language. If you want to understand more about this please read the following Blog series (part 1 to 23): Power Query M Primer (Part 23): Query Folding I | Ben Gribaudo

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZBBDsQgCEXv4romoIBylqb3v8YAmerY2MzGkOd/iJxnwnQkLNVOQMxQADKIFQA7dB1nKsN4S9SRaF3tJJbgNPjXd8hWMMnSi6MXbJk7MpzXSPudMki3oopz4J7NYkvr/dMnckNXgxYjEghrpG0ivmHQOBtmVLZJYWx4IhrvYtkoBEvX+j9CMZt/Am07VVuxSMnSxTeHqBlZXOP5Mk/neX+3lREJdH0A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [S.No = _t, ID = _t, OPS = _t, PP = _t]),
  #"Change Data Types" = Table.TransformColumnTypes(Source,{{"S.No", Int64.Type}, {"ID", Int64.Type}}),
  Buffer = Table.Buffer ( #"Change Data Types" ),
    #"Replace Blank with Null" = Table.ReplaceValue(Buffer,"",null,Replacer.ReplaceValue,{"S.No", "OPS", "PP"}),
  #"Add Conditional Result" = Table.AddColumn(#"Replace Blank with Null", "Condition Result", each 
    let 
      i=[S.No], x=[ID], OPS=[OPS], PP=[PP], t=#"Replace Blank with Null",
      text1 = Text.Length(OPS), text2 = Text.Length(PP),
      test_1 = 
        if OPS = PP and not ( text1 = null or text2 = null ) 
        then "Match" 
        else null,
      test_2 = 
        if ( text1 = null or text2 = null ) 
        then 
          if
            List.ContainsAny( 
              List.RemoveNulls(
                List.Combine( 
                  { 
                    Table.SelectRows( t , each [ID] = x and ( [S.No] = i - 1 or [S.No] = i + 1) )[OPS],
                    Table.SelectRows( t , each [ID] = x and ( [S.No] = i - 1 or [S.No] = i + 1) )[PP]
                  } 
                )
               ) ,
              List.RemoveNulls( { OPS , PP } )
            ) 
          then "Extra Population" 
          else null
        else null,
      test_3 = 
        if OPS <> PP and not ( text1 = null or text2 = null ) 
        then "Non Match" 
        else null,
      test_4 = if ( text1 = null and text2 = null ) then "Blank Value" else null,
      Result = List.First( List.RemoveNulls( { test_1, test_2, test_3, test_4, "Missed by ML" } ) )
    in 
      Result
  )
in
  #"Add Conditional Result"

Anonymous
Not applicable
4 years ago
Hi Daryl-Lynch-Bzy,

Thank you so much for your support its working fine as per excepted output.
one more help,
could you please tell me how to add above condition for multiple field like this below highlight each is color.Each is of different field in one sheet1 as below screen shot

i can only add for one field in the advanced editor , for remaining field if i used the same condition it throws an error Error : Token EOF Excepted

Could you please help me out to fix this issue

#"Added Index" = Table.AddIndexColumn(#"Changed Type2", "Index", 1, 1, Int64.Type),
#"Renamed Columns2" = Table.RenameColumns(#"Added Index",{{"Index", "S.No"}}),
Buffer = Table.Buffer ( #"Renamed Columns2" ),
#"Replace Blank with Null" = Table.ReplaceValue(Buffer,"",null,Replacer.ReplaceValue,{"S.No", "Attorney_Amount_ops", "Attorney_Amount_pp","Blanket_Indicator_ops","Blanket_Indicator_pp"}),
#"Add Conditional Result" = Table.AddColumn(#"Replace Blank with Null", "Condition Result", each
let
i=[S.No], x=[Image], Attorney_Amount_ops=[Attorney_Amount_ops], Attorney_Amount_pp=[Attorney_Amount_pp], t=#"Replace Blank with Null",
text1 = Text.Length(Attorney_Amount_ops), text2 = Text.Length(Attorney_Amount_pp),text3=Text.Length(Blanket_Indicator_ops), text4 = Text.Length(Blanket_Indicator_pp),
test_1 =
if Attorney_Amount_ops = Attorney_Amount_pp and not ( text1 = null or text2 = null )
then "Match"
else null,
test_2 =
if ( text1 = null or text2 = null )
then
if
List.ContainsAny(
List.RemoveNulls(
List.Combine(
{
Table.SelectRows( t , each [Image] = x and ( [S.No] = i - 1 or [S.No] = i + 1) )[Attorney_Amount_ops],
Table.SelectRows( t , each [Image] = x and ( [S.No] = i - 1 or [S.No] = i + 1) )[Attorney_Amount_pp]
}
)
) ,
List.RemoveNulls( { Attorney_Amount_ops , Attorney_Amount_pp } )
)
then "Extra Population"
else null
else null,
test_3 =
if Attorney_Amount_ops <> Attorney_Amount_pp and not ( text1 = null or text2 = null )
then "Non Match"
else null,
test_4 =
if ( text1 = null and text2 = null ) then "Blank Value" else null,
Result = List.First( List.RemoveNulls( { test_1, test_2, test_3, test_4,"Missed by ML" } ) )
in
Result
),
#"Renamed Columns3" = Table.RenameColumns(#"Add Conditional Result",{{"Condition Result", "Attorney_Condition Result"}})
in
#"Renamed Columns3"
"Add Conditional Result1" = Table.AddColumn(#"Replace Blank with Null", "Condition Result", each
let
i=[S.No], x=[Image], Blanket_Indicator_ops=[Blanket_Indicator_ops],Blanket_Indicator_pp=[Blanket_Indicator_pp] , t=#"Replace Blank with Null",
text3=Text.Length(Blanket_Indicator_ops), text4 = Text.Length(Blanket_Indicator_pp),
test_11 =
if Blanket_Indicator_ops = Blanket_Indicator_pp and not ( text3 = null or text4 = null )
then "Match"
else null,
test_12 =
if ( text1 = null or text2 = null )
then
if
List.ContainsAny(
List.RemoveNulls(
List.Combine(
{
Table.SelectRows( t , each [Image] = x and ( [S.No] = i - 1 or [S.No] = i + 1) )[Blanket_Indicator_ops],
Table.SelectRows( t , each [Image] = x and ( [S.No] = i - 1 or [S.No] = i + 1) )[Blanket_Indicator_pp]
}
)
) ,
List.RemoveNulls( { Blanket_Indicator_ops , Blanket_Indicator_pp } )
)
then "Extra Population"
else null
else null,
test_13 =
if Blanket_Indicator_ops <> Blanket_Indicator_pp and not ( text3 = null or text4 = null )
then "Non Match"
else null,
test_14 =
if ( text3 = null and text4 = null ) then "Blank Value" else null,
Result1 = List.First( List.RemoveNulls( { test_11, test_12, test_13, test_14,"Missed by ML" } ) )
in
Result1
),
#"Renamed Columns4" = Table.RenameColumns(#"Add Conditional Result1",{{"Condition Result", "Blanket_Condition Result"}})
in
#"Renamed Columns4"

Error : Token EOF Excepted

Forum Discussion

Conditional If or Switch using var DAX

Related content

Using Switch

Conditional Formatting using DAX

Unable to use conditional formating on Switch measure

Using negation condition in switch function

Conditional formating using DAX