Filter a table based on a column in another table

[Anonymous]

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.

[ERD]

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]

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]

@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! 

[Anonymous]

I created the measure fine, thanks.

When I apply the filter to a visual/data table it is greyed out so I cannot set "Is Not 0" like you demostrated. 

[ERD]

@Anonymous ,

It's hard to answer since I'm not able to see your file.

Please, find the demo attached: demo.pbix to compare with your file. Hope this helps.

[Anonymous]

I think the issue is that some of my visuals alread have a measure on, so I cannot apply this second measure as a filter.

Is there another method to achieve the same result?

Really appreciate your help.

[v-kelly-msft]

Hi @Anonymous ,

You could create a table as below:

Filtered Table = EXCEPT(SELECTCOLUMNS('Table1',"Number",'Table1'[Numbers]),'Table2')

And you will see:

If measure is not a good solution,you could try calculated column as below:

Column = 
var _tab=EXCEPT(VALUES('Table1'[Numbers]),'Table2')
Return
IF('Table1'[Numbers] in _tab,'Table1'[Numbers],BLANK())

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

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

[Anonymous]

Hi, thanks for the suggestion.

For the table I get the following error:

"A single value for column 'Numbers' in table 'Table1' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

For the column I get this error:

"The column 'Table1'[Column] cannot be pushed to the remote data source and cannot be used in this scenario."

[v-kelly-msft]

Hi @Anonymous ，

Is your issue solved now?If not ,could you pls upload your .pbix file or take a screenshot of the error you get?

Best Regards,
Kelly

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

[v-kelly-msft]

Hi @Anonymous ，

Could you pls upload your .pbix file?I cant figure out the reason without testing the actual data.

Remember to remove the confidential information.

Best Regards,
Kelly

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

[Anonymous]

Hello, thanks for following up.

This issues is not solved.

I can't upload my pbix as it uses DirectQuery connections so won't be possible to share for a demo.

[v-kelly-msft]

Hi  @Anonymous，

Could you pls take some screenshots to let me know more about your issue?

Best Regards,
Kelly

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

[Anonymous]

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

[amitchandak]

@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]

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"