Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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
Thanks for your suggestion, can you please explain the SUMMARIZE part ? I don't understand what happens when we SUMMARIZE two different tables unlinked ?
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
User | Count |
---|---|
98 | |
90 | |
83 | |
70 | |
67 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |