Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Using Slicer selected value to change which relationship is active

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!

 

  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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)

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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.