Dax Function to filter on a field, not a scalar value

[Bebs]

Hi,

In Dax, I would like to filter a column with the values of another column.

exemple : In a table If I've 2 columns CustomerName and ProductName.  also I've CustomerNameWhiteList  wich is a table/column result of a DAX Calculation.
I would like to get ProductName where CustomerName is not in a CustomerNameWhiteList

How can I do that ?

Another way to see the problem : I'd like to filter columns of a table using not a scalar value but all values of another field, How can I do that ?

Thanks

[TomMartens]

Hey @Bebs ,

you can not use SUMMARIZE with disconnected tables. Here you will find a description of SUMMARIZE: SUMMARIZE – DAX Guide

To avoid any further misunderstandings I recommend that you create a pbix file that contains sample data but still reflects your semantic model (tables, relationships, calculated columns, and measures). Upload the pbix to OneDrive, Google Drive, or Dropbox, and share the link in this thread. If you are using a spreadsheet to create the sample data instead of the manual input mehtod, share the spreadsheet as well.

Do not forget to describe the expected result based on the sample data you provide.

Regards,

Tom

[Bebs]

Thanks for your suggestion, can you please explain the SUMMARIZE part ? I don't understand what happens when we SUMMARIZE two different tables unlinked ?

[TomMartens · Accepted solution]

Hey @Bebs ,

the measure below returns a table, the funtion CONCATENATEX is only used to transform the table into a string:

Measure = 
var ProductsWhitelistedCustomers = 
    DISTINCT(
        SELECTCOLUMNS(
            FILTER(
                SUMMARIZE(
                    'Table'
                    , 'Table'[Products]
                    , 'Table'[CustomersWhitelist]
                )
                , 'Table'[CustomersWhitelist] <> BLANK()
            )
            , "PnotWhitelisted" , 'Table'[Products]
        )
    )
var ProductCustomers = 
    DISTINCT(
        SELECTCOLUMNS(
            SUMMARIZE(
                'Table'
                , 'Table'[Products]
                ,'Table'[all customers]
            )
            , "Products" , 'Table'[Products]
        )
    )
return
CONCATENATEX(
    EXCEPT( ProductCustomers , ProductsWhitelistedCustomers )
    , [Products]
    , ", "
)

The screenshot below shows my "table" and a Card visual that is visualizing the measure:

Hopefully, this provides you an idea of how tackle your challenge.

Regards,

Tom