cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Humpjs Frequent Visitor
Frequent Visitor

Dynamic List Selection in Power BI

I'm new to Power BI as our company is about to roll it out and I'm one of the first to have to learn it.   Been perusing this site daily and have learned so much already.     However, I've reached an impass.  The answer may already be on here somewhere, but I'm not quite sure how to pose the question I need help with.

 

Let's say I have a table which contains 4 columns:

Franchisee Name, District Name, Store Name, Sales

 

Untitled.jpg

 

I want add a slicer/option box/whatever that gives me the choice of selecting Franchisee, District Name, or Store Name.    I then want that choice to drive what populates my dropdown list in another slicer, which in turn populates a chart.  For example, if I selected Franchisee, my dropdown list in slicer would be Bingo, Longhorn, Central Florida, Courier.   If I select District Name, it would be Dallas, New York, San Fran, Chicago, Miami.  If I chose Store Name, it would be the list of stores under store name.

I did this in Access with Option buttons that dictated what populated my list box.

Hope this makes sense.   Thanks in advance for any help or suggestions.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Dynamic List Selection in Power BI

@Humpjs

 

Here is another method you can use, similar logic to @Vvelarde but with physical relationships.

It's based on @scottsen's post here: Tiny Lizard - Dynamically changing chart axis

 

Example pbix here.

 

  1. Set up tables like this:
    Sales
    Capture-sales.PNG

    Stores 
    Capture-stores.PNG

    Store Filter Type
    Capture-storehierarchy.PNG

    Sample DAX:

    Store Filter Type =
    VAR FranchiseeTable =
        SELECTCOLUMNS (
            Stores,
            "Store Name", Stores[Store Name],
            "Type", "Franchisee Name",
            "Value", Stores[Franchisee Name]
        )
    VAR DistrictTable =
        SELECTCOLUMNS (
            Stores,
            "Store Name", Stores[Store Name],
            "Type", "District Name",
            "Value", Stores[District Name]
        )
    VAR StoreTable =
        SELECTCOLUMNS (
            Stores,
            "Store Name", Stores[Store Name],
            "Type", "Store Name",
            "Value", Stores[Store Name]
        )
    RETURN
        UNION ( FranchiseeTable, DistrictTable, StoreTable )

     

     

  2. Create relationships as follows (bidirectional between Stores & Store Filter Type):
    Capture.png

     

  3.  Then you can simply filter on 'Store Filter Type'[Type], with 'Store Filter Type'[Value] on the visual's axis, with a SUM ( Sales[Sales] ) measure. You still have the ability to filter on Stores if you want, or you could eliminate Franchisee/District columns from Stores.

Regards,

Owen



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




5 REPLIES 5
Reid_Havens Member
Member

Re: Dynamic List Selection in Power BI

Hi there,

 

Happy to help. To my knowledge there isn't a way to do that with a slicer, since they require a single column to use as the filter selections. Probably not the answer you're wanting to hear or looking for sadly. I've yet to come across any clever workaround that can accomplish this, but if another expert user hops on here with one that would be a great feature to have.

 

Reid Havens - Principal Consultant

PowerPivotPro

Highlighted
Super User
Super User

Re: Dynamic List Selection in Power BI

@Humpjs

 

Hi, I found a way to solve it 

 

Steps:

 

1. Create a New Table: 

 

Table =
UNION (
    ADDCOLUMNS ( VALUES ( Table1[Franchise Name] ), "Type", "Franchise" ),
    ADDCOLUMNS ( VALUES ( Table1[District Name] ), "Type", "District" ),
    ADDCOLUMNS ( VALUES ( Table1[Store Name] ), "Type", "Store" )
)

Change the Name of the Column to Options.

 

2. Create a Measure

 

SalesM =
SWITCH (
    VALUES ( 'Table'[Type] ),
    "District", CALCULATE (
        SUM ( Table1[Sales] ),
        INTERSECT ( VALUES ( Table1[District Name] ), VALUES ( 'Table'[Options] ) )
    ),
    "Franchise", CALCULATE (
        SUM ( Table1[Sales] ),
        INTERSECT ( VALUES ( Table1[Franchise Name] ), VALUES ( 'Table'[Options] ) )
    ),
    "Store", CALCULATE (
        SUM ( Table1[Sales] ),
        INTERSECT ( VALUES ( Table1[Store Name] ), VALUES ( 'Table'[Options] ) )
    )
)

3. Now insert your visuals and Ready (Only test it for your scenario)

 

Franchise.pngStores.png




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Reid_Havens Member
Member

Re: Dynamic List Selection in Power BI

@Vvelarde that's actually a very great solution. I was noodling on a vew ways to do it with joins but didn't want to post anything overly complicated. However you presented a very good solution. Double kudos if I could.

Super User
Super User

Re: Dynamic List Selection in Power BI

@Humpjs

 

Here is another method you can use, similar logic to @Vvelarde but with physical relationships.

It's based on @scottsen's post here: Tiny Lizard - Dynamically changing chart axis

 

Example pbix here.

 

  1. Set up tables like this:
    Sales
    Capture-sales.PNG

    Stores 
    Capture-stores.PNG

    Store Filter Type
    Capture-storehierarchy.PNG

    Sample DAX:

    Store Filter Type =
    VAR FranchiseeTable =
        SELECTCOLUMNS (
            Stores,
            "Store Name", Stores[Store Name],
            "Type", "Franchisee Name",
            "Value", Stores[Franchisee Name]
        )
    VAR DistrictTable =
        SELECTCOLUMNS (
            Stores,
            "Store Name", Stores[Store Name],
            "Type", "District Name",
            "Value", Stores[District Name]
        )
    VAR StoreTable =
        SELECTCOLUMNS (
            Stores,
            "Store Name", Stores[Store Name],
            "Type", "Store Name",
            "Value", Stores[Store Name]
        )
    RETURN
        UNION ( FranchiseeTable, DistrictTable, StoreTable )

     

     

  2. Create relationships as follows (bidirectional between Stores & Store Filter Type):
    Capture.png

     

  3.  Then you can simply filter on 'Store Filter Type'[Type], with 'Store Filter Type'[Value] on the visual's axis, with a SUM ( Sales[Sales] ) measure. You still have the ability to filter on Stores if you want, or you could eliminate Franchisee/District columns from Stores.

Regards,

Owen



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Humpjs Frequent Visitor
Frequent Visitor

Re: Dynamic List Selection in Power BI

Many thanks to both Vvelarde and OwenAuger.   Went with Owen's solution because my real world application of this has much more than just Sales and it was easier to set up for me.    Once I'm a little more proficient, I can use Vvelarde's method.

 

I've learned more from this forum in 2 weeks than I have from any "training".