Conditional If or Switch using var DAX

[Anonymous]

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

@amitchandak @Jihwan_Kim @truptis @AlexisOlson @MFelix 

[Daryl-Lynch-Bzy · Accepted solution]

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]

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 

[Daryl-Lynch-Bzy]

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]

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 

[Anonymous]

Hi @Daryl-Lynch-Bzy 

Can you send your Sample working Power Bi file here ?

So that it will be usefull for review and to replicate the same.

Thank you, 

[Daryl-Lynch-Bzy]

Hi @Anonymous - the M query above can be copied into a blank query using the Advanced Editor.  Everything is contained in this single query.  I have used the "Enter Data" to replicate your original example.  You can add more sample data to check if it works as you had described.