Create a slicer that filters data dynamically

[otto-user101]

I submitted a question yesterday for this with some helpful solutions, but I'm taking another jab to explain my situation better. The dataset includes Primary and Alternate (sub) records with attributes populated for all records. I'm looking for a solution that allows the user to see how the overall composition changes by way of visuals (pie chart/bar charts) if a single primary is replaced with an alternative (sub). The solution to my first question allows me to create a measure that populates a field within a table/matrix that achieves this. However, a measure can't be used as a slicer so here's my second go at this.
 

Base table

ID	Name	Key	Attribute/Color	If Primary - Has Sub	On Sale
1	Lulu	Primary	Red	Yes	Yes
1	Caitlin	Sub	Blue	No	No
1	Ashe	Sub	Yellow	No	Yes
2	Garen	Primary	Orange	No	No
3	Poppy	Primary	Black	No	No
4	Veigar	Primary	White	Yes	Yes
4	Lux	Sub	Red	No	Yes
5	Soraka	Primary	Blue	No	Yes
6	Poro	Primary	Yellow	Yes	No
6	Akshan	Sub	Orange	No	No
6	Mundo	Sub	Black	No	No

Disconnected Table = calculatetable(summarize('Primary Table','Primary Table'[Name]),filter('Primary Table','Primary Table'[If Primary - Has Sub]="Yes"))

Name

Lulu

Veigar

Poro

Slicer1 = 'Disconnected Table'[Name]
Desired Slicer = Displays Names where ID matches that of Slicer1 selection e.g., If Slicer1 = "Lulu" then 'Lulu', 'Caitlin', 'Ashe' is available for selection

Desired Outcome: 1) populates selected record in Desired Slicer excluding all others records where ID matches that of Slicer1 selection , 2)populate all records where Key = 'Primary' where ID does not match Slicer1 selection and excludes all Sub where ID does not match Slicer1 selection

If Slicer1 = 'Lulu' and Desired Slicer='Caitlin', populates table to include only Caitlin where ID = 1, else if key = 'Primary' include, else exclude

Filtered Primary Table if Slicer1 = "Lulu" & Desired Slicer = 'Caitlin'

ID	Name	Key	Attribute/Color	If Primary - Has Sub	On Sale
1	Caitlin	Sub	Blue	No	No
2	Garen	Primary	Orange	No	No
3	Poppy	Primary	Black	No	No
4	Veigar	Primary	White	Yes	Yes
5	Soraka	Primary	Blue	No	Yes
6	Poro	Primary	Yellow	Yes	No

Filtered Primary Table if Slicer1 = "Lulu" & Desired Slicer = 'Lulu'

ID	Name	Key	Attribute/Color	If Primary - Has Sub	On Sale
1	Lulu	Primary	Red	Yes	Yes
2	Garen	Primary	Orange	No	No
3	Poppy	Primary	Black	No	No
4	Veigar	Primary	White	Yes	Yes
5	Soraka	Primary	Blue	No	Yes
6	Poro	Primary	Yellow	Yes	No

Filtered Primary Table if Slicer1 = "Lulu" & Desired Slicer = 'Ashe'

ID	Name	Key	Attribute/Color	If Primary - Has Sub	On Sale
1	Ashe	Sub	Yellow	No	Yes
2	Garen	Primary	Orange	No	No
3	Poppy	Primary	Black	No	No
4	Veigar	Primary	White	Yes	Yes
5	Soraka	Primary	Blue	No	Yes
6	Poro	Primary	Yellow	Yes	No

[v-junyant-msft]

Hi @otto-user101 ,

Please try this way:
First I use this DAX to create a new table to create Desired Slicer:

Desired Slicer = SELECTCOLUMNS (
    'Primary Table',
    "ID", 'Primary Table'[ID],
    "Name", 'Primary Table'[Name]
)

Then I create a measure by using this DAX in table Desired Slicer:

Measure = 
VAR A = CALCULATE(
    MAX('Desired Slicer'[ID]),
    FILTER(
        ALLSELECTED('Desired Slicer'),
        'Desired Slicer'[Name] = SELECTEDVALUE('Disconnected Table'[Name])
    )
)
RETURN

IF(
    ISFILTERED('Desired Slicer'), 
    IF(
        SELECTEDVALUE('Desired Slicer'[ID]) = A,
        1,
        0
    ),
    0
)

And the Desired Slicer is created, sorry I didn't change the name, you can change it yourself:

Then I create a measure by using DAX below in Primary Table:

Measure 2 = 
VAR A = CALCULATE(
    MAX('Primary Table'[ID]),
    FILTER(
        ALLSELECTED('Primary Table'),
        'Primary Table'[Name] = SELECTEDVALUE('Disconnected Table'[Name])
    )
)
RETURN
IF(
    ISFILTERED('Desired Slicer'[Name]) && ISFILTERED('Disconnected Table'[Name]),
    IF(
        SELECTEDVALUE('Primary Table'[Name]) = SELECTEDVALUE('Desired Slicer'[Name]),
        1,
        IF(
            SELECTEDVALUE('Primary Table'[ID]) <> A && SELECTEDVALUE('Primary Table'[Key]) = "Primary",
            1,
            0
        )
    ),
    0
)

The final result is shown below:

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

[otto-user101 · Accepted solution]

For anyone looking to achieve the same in Power BI, I used crossjoin to create a copy of the base table then created a custom column to serve as my slicer field.

[AmiraBedh]

DAX does not allow direct filtering between tables without a relationship, the logic says that you need to establish a connection.
One way to do this is by creating a column in your primary table that matches the values in the disconnected table.

You can create a new column in your primary table that identifies if the record is related to one of the names in the disconnected table. This can be a simple boolean flag (True/False).

Then, ceate measures that dynamically filter the primary table based on the selection in Slicer1 and the Desired Slicer.

Use these measures in your visuals to reflect the changes based on the slicer selections.

I don't have your model so I am imagining something like this :

Connected Column =
IF(
'Primary Table'[Name] IN VALUES('Disconnected Table'[Name]),
TRUE(),
FALSE()
)

Filtered Data Measure =
VAR selectedPrimary = SELECTEDVALUE('Disconnected Table'[Name])
VAR selectedSub = SELECTEDVALUE('Primary Table'[Name], "All")
RETURN
CALCULATE(
COUNTROWS('Primary Table'),
FILTER(
'Primary Table',
IF(
'Primary Table'[Key] = "Primary" && 'Primary Table'[Connected Column] = TRUE(),
'Primary Table'[Name] = selectedPrimary || selectedSub = "All",
'Primary Table'[Key] = "Primary"
)
)
)