topic Re: Using Slicer selected value to change which relationship is active in DAX Commands and Tips

Using Slicer selected value to change which relationship is active

Anonymous — Fri, 29 Jul 2022 11:16:51 GMT

I currently have a table where a cell (Title) can contain multiple Brand values, so I made for each Brand a check to see if it is contained in that Title - visible under the BrandNames table, if yes then it will return the name of the brand.
Then I used the Distinct function in the BrandNameList table to get all unique brand names.

Now I want to have this slicer (which is the values from the BrandNameList) to filter the BrandNames table.

The Relationship works for 1 column in the BrandNames table as it is active, in this case "AUDI", but it does not work for the other brands since their relationship is inactive.

How can I have the slicer filter all the values based on what brands are found in the title using the USERELATIONSHIP() DAX function or a complete other way?

(The main issue is that 1 cell can contain multiple brands)

I have looked up how to do this but found nothing, please help!

Re: Using Slicer selected value to change which relationship is active

amitchandak — Fri, 29 Jul 2022 12:09:16 GMT

Anonymous , Do not join or make all inactive

measure =

Var _tab = values(Slicer[Brand])

return

calculate(Sum(Table[Value]), filter(Table, Table[Brand 1] in Tab && Table1[Brand 2] in _tab))

Add more, use OR (||) if needed

Re: Using Slicer selected value to change which relationship is active

Anonymous — Fri, 29 Jul 2022 14:12:52 GMT

Currently my DAX looks like this:

BrandSlicer =

Var _tab = VALUES(BrandNameList[BrandNames])

return

CALCULATE(SUM(BrandNameList[BrandNames]), FILTER(BrandNames,

BrandNames[AUDI] in _Tab &&

BrandNames[VW] in _tab &&

BrandNames[SBA] in _tab &&

BrandNames[CUPRA] in _tab &&

BrandNames[SKODA] in _tab))

But I am unsure what to put as the Table after SUM(
because if I use it the way I wrote and put the measure as a filter on a visual it does not work.
(I have also removed all the relationships prior to doing this)

Re: Using Slicer selected value to change which relationship is active

Anonymous — Wed, 03 Aug 2022 06:23:49 GMT

Hi Anonymous ,

If you want to add the measure into filter, you may try this code to create a measure. I suggest you to change all relationships between two tables to inactive.

BrandSlicer = VAR _tab = VALUES ( BrandNameList[BrandNames] ) RETURN IF ( MAX ( BrandNames[AUDI] ) IN _tab || MAX ( BrandNames[VW] ) IN _tab || MAX ( BrandNames[SBA] ) IN _tab || MAX ( BrandNames[CUPRA] ) IN _tab || MAX ( BrandNames[SKODA] ) IN _tab, 1, 0 )

Then add this measure into the visual level filter in your visual and set it to show items when value =1.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.