Is there something like Qlikview's "Lock selection" in Power BI?

[jamunategui]

Hi,

I'm looking for a way to replicate "lock selections" Qlikview 's function in Power BI.

And example of what I'm trying to get for those that don't know how it works:

In our entity we offer various services.

By selecting one of them in a slicer, I can see which people used it. Then, I need to know what other of our services were used by those previously selected people (and not others).

Hope the question is clear.

Thank you,

Juanan Amunategui

[PaulDBrown]

Can you provide a sample dataset or PBIX file?

[jamunategui]

Hi Paul. 

I'll try to clarify with some data. 

Suppose I have three tables like these:

Persona (people who uses our services)

Servicio (the services we offer) and

Interactions between one another: real uses of services by pleople.

In my report I have a slicer for services. When I select i.e. "Diurno", I can see the persons who used this services (Bittor, Kepa, Mikel) as you can see in the image below.

Now I want to know which OTHER SERVICES have used the persons obtained in the first query. It's very simple and easy to do with just a few values.

I could use another slicer with persons, select these three people and clear the first slicer as seen below.

But, in my real data I could get thousands of persons that used one of my services and it'd be impossible to do it manually. It was very easy to achieve in Qlikview using "Select possible values" in the slicer of persons, and clearing the rest.

Is there a way to do it in Power BI?

Thanks in advance. Hope you can understand the question.

Juanan Amunategui

[PaulDBrown]

@jamunategui 

One way of doing this is:

1) Duplicate the main table and leave it disconnected from the other tables in the model

2) Create the following measures to be used in the filter pane for the table visual including the fields from the disconnected table:

Values persona =
VAR Filtered =
    VALUES ( 'Disconnected Table'[Persona] )
VAR DimPersona =
    VALUES ( 'Table'[Persona] )
RETURN
    COUNTROWS ( INTERSECT ( Filtered, DimPersona ) )

Values Servicio =
VAR Slicer =
    VALUES ( Servicio[Servicio] )
VAR DiscTable =
    VALUES ( 'Disconnected Table'[Servicio] )
RETURN
    COUNTROWS ( EXCEPT ( DiscTable, Slicer ) )

Add the Slicer and the visual for the table showing the persona values from the slicer. Add a table visual with the persona and servicio fields from the Disconnected Table. To this last table, add the measures to the filters on the visual in the filter pane and set the values to 1 and you will get this:

I have attached the sample PBIX file to the message for you to check out

[jamunategui]

Thank you very much !!! @PaulDBrown. 

It works fine and solves my problem.👏

Great!

I don't like the need to duplicate my 500k row (and growing) fact table, but if there is no other way to get this result I'll have to do it this way. If anyone discovers another approach I'll be glad to know it.

Juanan

[PaulDBrown · Accepted solution]

@jamunategui 

Apologies upfront since the solution I provided is of course very inefficient. I'm not sure why I didn't think of the obvious alternative, but here it is.

1) instead of duplicating the main table, create a duplicate of the "Servicio" Dimension table:

2) Set up the report page with the a visual to show the filtered personas and servicios, add a slicer from the disconnected Slicer table and another from the main servicio dimension table (which we will use to filter the visual via the relationship in the model). Select the option "Synch Slicers" from the ribbon, select the slicers and give them the same group name in the advnaced options:

Now both slicers will interact and be in synch.

3) Next add the visual to show other services, Select the Servicio slicer, and under format, enable "Edit Interactions" and turn off the filter for the new "other services"

4) Now we hide the "Servicio Slicer" by enabling the "Selection Pane" under View in the ribbon. Turn off the eye icon for the Servicio slicer.

5) Next create two measures to be used in the filter pane for the "Other Services" visual

Values persona = 
COUNTROWS(
    CALCULATETABLE(VALUES('Table'[Persona]), 
    TREATAS(VALUES('Disconn Serv. Slicer'[Servicio]), 'Table'[Servicio])))

Values Otros Servicios = 
VAR Slicer = VALUES('Disconn Serv. Slicer'[Servicio])
VAR DiscTable = VALUES('Table'[Servicio])
RETURN
COUNTROWS(
    EXCEPT(DiscTable, Slicer))

I've attached the new file for your reference

[jamunategui]

Hi @PaulDBrown 

Well, this solution is all but obvious 😝

Thank you VERY much. I need to test it with real data, but it seems to be the right way to do it.

Juanan