Slicer based on category header with drop down

[Anonymous]

Hi!

I have data over where people lived in different time periods. The time periods are with some distance apart and most importantly, new time periods will be added in the future to the dataset. 

I want to create some type of tool (preferably a slicer) which will let me select any city for any time period and combine these in different ways such that i can choose "all people who lived in Chicago during TP1 and those who lived in Seattle during TP4" or what ever combination.

Right now my data is structured like this (sorry for excel-file but masked because of customer-integrety):

When I just drag all the columns into the slicer-tool I get a slicer with the following structure:

Since Power BI wants me to select a combination in a hierarchy way. Would not be the end of the world if I did not have several timeperiods in the future to add on, and could most likely easily be solved with a pivot if it were just 2 time periods.

What I would like is a slicer-tool that just gives me the column headers as foldable items, like when one creates real hierarchys with year and months, but that I instead can click on what time-period I would want to look at and which cities from that particular period.

like this:

 Thanks in advance for any help and sorry for the masked and perhaps badly explained excel data.

 

[v-easonf-msft]

Hi,  @Anonymous 

Please try follow steps:

1.Duplicate a new table in PQ
2.Select columns "Period1","Period2","Period3"  of new table and then unpivot columns in the advanced editor as  shown below :

3.Create a  new measure and apply to viusal filter pane to filter data:

Measure1 = 
VAR flag =
    SWITCH (
        SELECTEDVALUE ( Slicer[Period Time] ),
        "Period1", IF ( SELECTEDVALUE ( 'Table'[Period1] ) = SELECTEDVALUE ( Slicer[City] ), 1, 0 ),
        "Period2", IF ( SELECTEDVALUE ( 'Table'[Period2] ) = SELECTEDVALUE ( Slicer[City] ), 1, 0 ),
        "Period3", IF ( SELECTEDVALUE ( 'Table'[Period3] ) = SELECTEDVALUE ( Slicer[City] ), 1, 0 ),
        0
    )
RETURN
    IF ( ISFILTERED ( Slicer[Period Time] ), flag, 1 )

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

It works like a charm for the slicer itself, but I'm having trouble connecting it to my original dashboard with the other stats from the original table. When ever I get them to operate togheter (read: that something happens when i click in the slider) I get a twice as high count/sum or what not I am displaying.

[v-easonf-msft]

Hi,  @Anonymous 

Can you share a sample file for further research?

Maybe there was an error when converting the data in pq, or the results need to be averaged.

Best Regards,
Community Support Team _ Eason