Forum Discussion

Humpjs's avatar
Humpjs
Icon for Advocate I rankAdvocate I
9 years ago
Solved

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...
Need Help
  • OwenAuger's avatar
    OwenAuger
    9 years ago

    Humpjs

     

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

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

     

    Example pbix here.

     

    1. Set up tables like this:
      Sales

      Stores 

      Store Filter Type

      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):

       

    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

Vvelarde's avatar
Vvelarde
Icon for Community Champion rankCommunity Champion
9 years ago

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)

 

Reid_Havens's avatar
Reid_Havens
Icon for Most Valuable Professional rankMost Valuable Professional
9 years ago

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.

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    9 years ago

    Humpjs

     

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

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

     

    Example pbix here.

     

    1. Set up tables like this:
      Sales

      Stores 

      Store Filter Type

      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):

       

    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