Forum Discussion

amay15's avatar
amay15
Frequent Visitor
8 years ago
Solved

Filter rows query with more than 1 column condition

I want to filter data as per below table

 

TestCost 1Cost 2 TestCost 1Cost 2
A010 A010
B00 C100
C100 E2020
D00    
E2020    

 

                Input                                                                                Output

 

IF Cost1= 0 & Cost2 = 0 then dont populate the row.

 

Any help will be appreciated

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    amay15

     

    Same logic we can apply in Query Editor as well

    File attached

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIAYkMDpVidaCUnKBfCc4ZIwLguKJKuQJaRAYSIjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Test = _t, #"Cost 1" = _t, #"Cost 2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Test", type text}, {"Cost 1", Int64.Type}, {"Cost 2", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.AllTrue({[Cost 1]=0, [Cost 2]=0})),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = false)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
in
    #"Removed Columns"

     

    Bascially add a custom column to check condition

     

    =List.AllTrue({[Cost 1]=0, [Cost 2]=0})

    Then filter the records

6 Replies

  • Stachu's avatar
    Stachu
    Icon for Community Champion rankCommunity Champion
    8 years ago

    I'm not sure I get the request - you need indication how to filter it in DAX for further calculations, or filter it in PowerQUery to reduce number of rows?

    The DAX for this should be folllowing

    Table Filtered = 
FILTER('Table','Table'[Cost 1]<>0 || 'Table'[Cost 2] <> 0)
    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Icon for Community Champion rankCommunity Champion
      8 years ago

      amay15

       

      Also you can create a column and then use it as a VISUAL Filter

       

      Column =
AND ( [Cost 1] = 0, [Cost 2] = 0 )

    • amay15's avatar
      amay15
      Frequent Visitor
      8 years ago

      I want to apply this at query level then load the data. I dont want to create Column after data load.

       

      Thank for quick reply. Looking forward for solution.

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Icon for Community Champion rankCommunity Champion
        8 years ago

        amay15

         

        Same logic we can apply in Query Editor as well

        File attached

         

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIAYkMDpVidaCUnKBfCc4ZIwLguKJKuQJaRAYSIjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Test = _t, #"Cost 1" = _t, #"Cost 2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Test", type text}, {"Cost 1", Int64.Type}, {"Cost 2", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.AllTrue({[Cost 1]=0, [Cost 2]=0})),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = false)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
in
    #"Removed Columns"

         

        Bascially add a custom column to check condition

         

        =List.AllTrue({[Cost 1]=0, [Cost 2]=0})

        Then filter the records