Dynamic multiple slicers

[Anonymous]

Hi everyone!

I'm stuck with the following matter: 

In my model I have 3 tables:

MODEL:

SALES:

PRODUCT_ID	VENDOR_ID	QUANTITY(€)	DATE
123	AAAAA	2	07/13/2020
221	BBBBB	5	07/13/2020

PRODUCTS:

PRODUCT_ID	CATEGORY	SUBCATEGORY
123	SPORTS	FOOTBALL
221	SLEEP	SHOES

VENDOR: 

VENDOR_ID	SHOP_TYPE	LOCATION
AAAAA	BIG	ITALY
BBBBB	SUPERMARKET	GERMANY

These tables (vendor and products) are connected by ID to sales table. 

WHAT I WANT TO DO?

I would like to have in the report, two slicers.

1) To select the "vision" that you want: Product or vendor. (Select slicer in the picture)

2) Select details, i.e: Product selected, and  the category of the product. (PRODUCT slicer in the picture)
Example: 

If I change to vendor, it should change insted of showing products (sleep and sports) I should be able to select "SHOP_TYPE"

Many thanks for your help! 🙂

[amitchandak]

@Anonymous , refer Dynamically change chart axis in Power BI
https://www.youtube.com/watch?v=6jeSIRpjv0M

Measure slicer

https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50...

[Greg_Deckler]

@Anonymous - See if this helps: https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-EVERYTHING-measures-axis-legend-titl...

[Anonymous]

Thanks for your answer, the link is great, but it's only working with one table and applying filters to it. In my case, I have multiple tables, and PowerBI should show info from one table or other.

[Greg_Deckler]

@Anonymous - One thought on this, use buttons instead of slicers for your "vision". Then you could leverage stacked visuals and bookmarks.

[MFelix · Accepted solution]

Hi @Anonymous ,

Create a table with both tables you need:

Type        Value

Product	Sports
Product	Sleep
Vendor	AAAAA
Vendor	BBBBB

Now do the following measure:

Sum Sales =
VAR SelectedType =
    SELECTEDVALUES ( Slicer[Type] )
VAR Selected_Value =
    VALUES ( Slicer[Value] )
RETURN
    SWITCH (
        SelectedType ;
        "Vendor"; CALCULATE ( SUM ( Sales[QUANTITY(€)  ] ); Vendors[VENDOR_ID  ] IN Selected_Value);
        CALCULATE ( SUM ( Sales[QUANTITY(€)  ] ); Products[CATEGORY  ] IN Selected_Value)
    )

Now use the measure as your values on the charts as you can see in attach file altough I am selecting on products and vendor one of the visualizations is made on subcategory.