Dynamic filtering the values on disconnected slicer with WHERE clause

[ladybel]

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

[ladybel]

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

[Anonymous]

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

[ladybel]

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

[Anonymous]

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

[ladybel]

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.

[ladybel]

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

[Anonymous]

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

[ladybel]

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

[ladybel]

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! 

[Anonymous]

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