topic Dynamic filtering the values on disconnected slicer with WHERE clause in DAX Commands and Tips

Dynamic filtering the values on disconnected slicer with WHERE clause

ladybel — Wed, 08 Jun 2022 14:56:20 GMT

The problem:

Let the data structure be following for the simplicity: Query1 has columns 'Name' and 'Brand' with many-to-many relationship ( one Name contains several Brands but also one Brand can be found in several Names) . However I need to filter on Brand and be able to see all the Names where at least one of the Brands is equal to the selected one(s).

I have created a diconnected table for the Brand and need your help to write a proper measure with the following logic:

Selector =

var selector = selectedvalue('disconnected table'[Brand])

var selected =

return all values of Query1[Name] where Query1[Brand] contains selector

return selected

I am quite new to DAX and tried to search but nothing I find is even close to my problem. I appreciate any help/link/hint.

Kind regards,

Maria

Re: Dynamic filtering the values on disconnected slicer with WHERE clause

Anonymous — Mon, 13 Jun 2022 08:31:00 GMT

Hi ladybel ,

Try this:

Selector = CALCULATETABLE ( VALUES ( Query1[Name] ), FILTER ( Query1, Query1[Brand] IN VALUES ( 'disconnected table'[Brand] ) ) )

It will return a table contains the [Names].

Best Regards,

Jay

Re: Dynamic filtering the values on disconnected slicer with WHERE clause

ladybel — Tue, 14 Jun 2022 08:19:34 GMT

Hello Jay, thanks a lot for your reaction, but I guess there is an opposite logic

I need: 'disconnected table'[Brand] in values (Query1[Brand]), but it gives an error, so how is it possible to write this correctly?

Kind regards,

Maria

Re: Dynamic filtering the values on disconnected slicer with WHERE clause

Anonymous — Tue, 14 Jun 2022 08:43:52 GMT

Hi ladybel ,

Return the 'disconnected table'[Brand] if it is in 'Query1'[Brand].

Selector = CALCULATETABLE ( VALUES ( 'disconnected table'[Brand] ), FILTER ( 'disconnected table', 'disconnected table'[Brand] IN VALUES ( Query1[Brand] ) ) )

Best Regards,

Jay

Re: Dynamic filtering the values on disconnected slicer with WHERE clause

ladybel — Tue, 14 Jun 2022 10:20:09 GMT

There is a problem, I need to return a Query1[Name], I use the disconnected table only as a tool for filtering for a brand. I don't filter on Query1[Brand] because it will filter out all the other options, and that is what I try to avoid.

There is a bit more complicated logic, hence this post 🙂

But thank you for trying to help me.

Re: Dynamic filtering the values on disconnected slicer with WHERE clause

ladybel — Tue, 14 Jun 2022 10:23:35 GMT

if I could you the SQL syntaxis, I would write something like this:

Selector = CALCULATETABLE ( VALUES ( Query1[Name] ), WHERE EXISTS Query1[Brand] = ( 'disconnected table'[Brand]) )

Re: Dynamic filtering the values on disconnected slicer with WHERE clause

Anonymous — Wed, 15 Jun 2022 10:05:30 GMT

Hi ladybel ,

From what I understand, the returned result should be the [Name] column in Query1 with the condition Query1's [Brand] exists in 'disconnected table', am I right?

It would be easier to understand if you could share some sample data and expected result.

Query1:

'disconnected table':

Expected result:

Best Regards,

Jay

Re: Dynamic filtering the values on disconnected slicer with WHERE clause

ladybel — Wed, 15 Jun 2022 10:21:06 GMT

Hello Jay,

'From what I understand, the returned result should be the [Name] column in Query1 with the condition Query1's [Brand] exists in 'disconnected table', am I right?' -actually the opposite 🙂

the values of the 'disconnected table'[Brand] should exist in the values of Query1's [Brand].

Lets say you have 3 tests with 4 products each. Each product can be be of brand A , B, C and D. In each test you normally have products of different brands.

Test1: Product1: brand A, Product2:Brand A, product3: brand A, Product 4:Brand B

Test2:Product1: brand C, Product2:Brand B, product3: brand C, Product 4:Brand B

Test3:Product1: brand D, Product2:Brand D, product3: brand A, Product 4:Brand C

Lets filter now in the 'disconnected table'[Brands], say I selected brand C

The desired output shoud be: Test2, Test3

Selected brands A and D

The desired output shoud be: Test1, Test3

Kind regards,

Maria

Re: Dynamic filtering the values on disconnected slicer with WHERE clause

ladybel — Wed, 15 Jun 2022 10:26:03 GMT

Actually I don't only need the test names, but also have to be able to visualize the tests.

So if I selected brand C, I want to see the visualization of the Test2 and Test3 with the complete data, for all the 4 products in each ! And what PowerBI normally does, it filters also on brand and I get only the data of those two tests but also only for the brand C!

Re: Dynamic filtering the values on disconnected slicer with WHERE clause

Anonymous — Fri, 17 Jun 2022 09:54:17 GMT

Hi ladybel ,

Are test1, test2 and test3 splitted tables that contain columns [product] and [brand]?

I might be able to create the DAX if you could share some sample data.

Best Regards,

Jay

Re: Dynamic filtering the values on disconnected slicer with WHERE clause

ladybel — Thu, 30 Jun 2022 08:55:24 GMT

Hello Jay, actually they are not splitted.. Will this help if I split them?

What do you mean by sharing a sample data ? I am not quite sure how I can do this..

Thanks a lot