Slicer to filter table before UNION

[Anonymous]

Hello PowerBi Community,

I searched the forum before and couldnt find anything that wopuld help my case.

I have two tables (Letters and Numbers).

Value table is the UNION between the two above.

When I select A on Letters slicer, the UNION should show me the Letter A only and ALL the numbers. 

Same should happen with the Numbers slicer.

I tried using CALCULATE table, but I cannot work out how to pass the SELECTEDVALUE from one slicer to the UNION formula.

Any help would be greatly appreciated.

Many thanks,

Tiberiu

[Anonymous · Accepted solution]

// First, you have to create
// the RIGHT union:

[Values] = // calculated table
UNION(
    SELECTCOLUMNS(
        Letters,
        "Value",
            Letters[Letter],
        "Table",
            "Letters"
    ),
    SELECTCOLUMNS(
        Numbers,
        "Value",
            // Since the column in the first
            // table is text, all the numbers
            // must be turned into text as well,
            // hence the suffix >>& ""<<.
            Numbers[Number] & "",
        "Table",
            "Numbers"
    )
)

// The column 'Values'[Table] MUST
// be hidden. It'll only be used
// by the filtering measure below.
// There should be *NO* relationship
// from any of the original tables
// to the one above.

// To the table Numbers add a hidden column
// called NumberAsText. This will help later
// with the filtering measure. The definition
// of the column is:
[NumberAsText] = Numbers[Number] & ""
    

// This is the filtering measure that you'll
// use in the visual's Filtering Pane and
// you'll only show the rows where the measure
// returns 1.
[Should Show Row?] =
IF( ISINSCOPE( 'Values'[Value] ),
    
    var vCurrentValue = SELECTEDVALUE( 'Values'[Value] )
    var vCurrentValueTable = SELECTEDVALUE( 'Values'[Table] )
    return
    SWITCH( TRUE(),

        // Selections made from both tables
        ISFILTERED( Letters ) && ISFILTERED( Numbers ),
            var vLetters = DISTINCT( Letters[Letter] )
            var vNumbers = DISTINCT( Numbers[NumberAsText] )
            var vShouldKeepRowVisible =
                or(
                    vCurrentValue in vLetters,
                    vCurrentValue in vNumbers
                )
            var vResult = int( vShouldKeepRowVisible )
            RETURN
                vResult,
        
        // Selection made from Letters only
        ISFILTERED( Letters ),
            var vLetters = DISTINCT( Letters[Letter] )
            var vShouldKeepRowVisible =
                or(
                    vCurrentValue in vLetters,
                    vCurrentValueTable = "Numbers"
                )
            var vResult = int( vShouldKeepRowVisible )
            return
                vResult,
        
        // Selection made from Numbers only
        ISFILTERED( Numbers ),
            var vNumbers = DISTINCT( Numbers[NumberAsText] )
            var vShouldKeepRowVisible =
                or(
                    vCurrentValue in vNumbers,
                    vCurrentValueTable = "Letters"
                )
            var vResult = int( vShouldKeepRowVisible )
            return
                vResult,

        // If nothing is filtered... show everything.
        1
    )
)

[Anonymous]

@Anonymous 

It works like a charm!  I am in awe!!

Thank you Daxer!!

If anyone wants to see the above script in action, please follow the link to download the PBIX file.

Thank you!

Tiberiu

Later edit:

Links are not allowed and I do not know how to share the actual PBIX file. I attached a screenshot instead.

Message me directly and I'll happilly send you a link.

Mant thanks again!

[Anonymous]

Thank you for taking the time to have a go at this.

I'll try it and let you know how it works for me.

Many thanks.

Tiberiu

[Fowmy]

@Anonymous 

When creating your Union Table, the model is updated with the results from both the tables, and values selected in the slicers are not visible at that stage. Slicer selections cannot influence the table. 

If you want to achieve this, you can create a measure and assign it to the union table on the filter pane under the visual filter.

I am not sure what is the use case here.

[Anonymous]

Apoogies for being too abstract.

The use case here is the following: 

1. in a region there are 15 homecares (the Letters)

2. in the same region there are 100 homecarers (the Numbers)

When I select a Homecare, I'd like to see it on a list along with all homecarers on a 20 mile radius, so that I can plot it on a map visual.

Hope it shed some light on the matter! 

Cheers!

Tiberiu