Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Filter a table based on a column in another table

I have a column of data that drives several visuals 'TABLE 1 - DATA'[Numbers]. There are several values in this column that are not applicable and need to be filtered out.

 

At the moment I am manually selecting each value I want to filter out on each visual. It works but it is not ideal as there are about 50 N/A values which change often and it takes a while to get each visual aligned.

 

I had the idea of creating a new table and column (TABLE 2 - N/A FILTER LIST'[N/A NUMBERS]) with the list of N/A values to use as a filter and have created a relationship between the the two tables as above.

 

I now want to filter 'TABLE 1 - DATA'[Numbers] to exclude the values listed in 'TABLE 2 - N/A FILTER LIST'[N/A Numbers].

 

I am really struggling with writing the DAX measure to do this and would really appreciate some assistance.

Need Help

15 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous ,

     

    If you need a new table

    Table 3 = except(Table1,table2)

     

    If need a measure

    countrows(filter(Table1, not( Table1[Number] in Table2[ N/A Number])))

     

    plot with Table1[Number] and other columns from Table1

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi, thanks for the quick reply.

       

      I tried the measure but get this error:

       

      "The function expects a table expression for argument '2', but a string or numberic expression was used"

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi, I tried all of these suggestions and couldn't get any of them to work as expected. Are there any other possible solutions?

  • ERD's avatar
    ERD
    Community Champion
    5 years ago

    Hello Anonymous ,

    You can use Power Query for filtering out n/a values:

    let
    Source = Table.NestedJoin(TData, {"Numbers"}, TFilterList, {"N/A numbers"}, "TFilterList", JoinKind.LeftAnti),
    #"Removed Columns" = Table.RemoveColumns(Source,{"TFilterList"})
in
    #"Removed Columns"

     

    Did I answer your question? Mark my post as a solution! 

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi, thanks for the reply.

       

      This solution is not possible as the data in 'TABLE 1 - DATA' comes from a DirectQuery connection in a composite model and I do not have option to alter the query like you would for an import connection.

      • ERD's avatar
        ERD
        Community Champion
        5 years ago

        Anonymous ,

        In case of measure, if you want to use method described by amitchandak, you need to use VALUES function for the measure to work correctly.

        filterMeasure = 
COUNTROWS (
            FILTER ( TData, NOT ( TData[Numbers] IN VALUES ( TFilterList[N/A numbers] ) ) )
        )

        Afterwards you can apply it to your table as filter:

         

        Did I answer your question? Mark my post as a solution!