Using Slicer selected value to change which relationship is active

[Anonymous]

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!

[amitchandak]

@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

[Anonymous]

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)

[v-rzhou-msft · Accepted solution]

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.