Forum Discussion

Anonymous's avatar
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

 

 

amitchandak Jihwan_Kim truptis AlexisOlson MFelix 

Need Help
Show and Tell
  • Anonymous's avatar
    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"

     

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    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's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Anonymous 

       

      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, 

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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.

    • Anonymous's avatar
      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's avatar
        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"