Using slicer to choose which dimension to display

[Ohad]

Hi,

I used the following article on http://breaking-bi.blogspot.ca/2016/06/power-bipowerpivot-using-slicers-to.html to create a slicer that gives the user the option to choose which measure to display in the chart and now I am wondering if it’s possible to do the same thing with the dimensions instead of the measures so the user will be able to choose the dimension dynamically using additional slicer? If so, how?

Regards,

Ohad

[Ohad]

@tjd

All the dimensions are the same type (text) and all the measures are the same type (numbers).

[v-huizhn-msft]

Hi @Ohad,

As I tested, we are unable to achieve it in Power Bi desktop as the @tjd posted. Thanks for understanding.

Best Regards,
Angelia

[Ohad]

@v-huizhn-msft

Thank you Angelina.

[Ohad]

@Phil_Seamark

Hi,

I have a table with the following columns:

1. Store_Name
2. Category_Group_Name
3. Subcategory_Name
4. Item_Name
5. Item_Quantity
6. Gross_Amount
7. Discount_Amount
8. Net_Sales_Amount

Instead of creating a separate chart for Item_Quantity and another one for Net_Sales_Amount (for example) I managed to create a slicer (using the link above) that will give the user the option to choose the measure (Y axis) using a slicer from the following columns:

1. Item_Quantity
2. Gross_Amount
3. Discount_Amount
4. Net_Sales_Amount

This is the formula that I am using so far for the measure:

Selected_Measure = IF(HASONEVALUE(Data_Measures[Measure]),SWITCH(FIRSTNONBLANK(Data_Measures[Measure],Data_Measures[Measure]),"Item_Quantity",SUM(Data[Item_Quantity]),"Gross_Amount",SUM(Data[Gross_Amount]),"Discount_Amount",SUM(Data[Discount_Amount]),"Net_Sales_Amount",SUM(Data[Net_Sales_Amount])))

Now I am looking to do the same for the dimension (X axis) and save the need of creating separate chart for Store_Name and another one for Category_Group_Name (for example), I would like to create an additional slicer that will give the user the option to choose from the following columns:

1. Store_Name
2. Category_Group_Name
3. Subcategory_Name
4. Item_Name

Hope that it’s clear now.

Thanks,

Ohad

[tjd]

I had previously asked that same question on the forum and was told that right now, you can only do this with measures, not categorical dimensions.  They suggested that I create a psuedo-hierarchy and use drill down to see the various categories on the x-axis.  It worked out fairly nicely and did the trick but would have liked the ability to use a slicer.  As an aside, the measures that you use in your example have to be all the same data type/format.  You cannot mix types of number, date, text, etc.

[Phil_Seamark]

Hi @Ohad,

Do you have an example of what you mean?