[SOLVED] Create a column with dynamic values based on selected value of slicer

Create a column with dynamic values based on selected value of slicer

Power BI enthusiast

I am new to Power BI. I have an issue with details as following:

I have a table called dw_xyz:

ID	Shape	Color	Value
1	Circle	Blue	10
2	Triangle	Red	23
3	Rectangle	Blue	20
4	Triangle	Blue	12

Then I created a slicer containing two values: 'Shape' and 'Color' (Represent two column headers of table dw_xyz)

I would like to create a dynamic pivot table based on selected value of the slicer. When the selected value is 'Shape', the pivot table would summarize data by column 'Shape'. Otherwise, when the selected value is 'Color', the pivot table would summirize data by column 'Color'.So what I had done were:

Create slicer (manually enter data) with two values: 'Shape' and 'Color'
Create 'new column' in table dw_xyz. This new column called 'dynamicColumn'

*not sure, using 'new column' or 'new measure' was recommended. Please advise on this too

I typed the DAX as following:

dynamicColumn=SWITCH(SELECTEDVALUE(slicer_reference),"Shape",[Shape],"Color",[Color])

When I did selection on slicer, values of dynamicColumn remained the same (no change as expected).

So, I tried to find another way. I created one additional table (outside dw_xyz) by DAX as following:

VAR X=Summarize by Shape
VAR Y=Summarize by Color
RETURN
UNION(X,Y)

And I pointed the new table as pivot table source, not dw_xyz. It worked as expected. However, I was not sure, what I tried was best practice. Any advice from you would be highly appreciated.

Solution

One way is to create a table with 2 columns (slicer: "shape/color", value: whatever) and then use value as the category of your table/chart, and a measure that shows the correct total depending on which type is selected in your slicer.

There is a good example in this video: https://www.youtube.com/watch?v=fktQmk0a9OI

Depending on the size/complexity of your data and what you're trying to achieve, one thing I prefer is to have instead of slicers, just buttons that show/hide the visual with the category I'd want. So in this example you'd have a Shape and a Color button and by clicking on one it makes the corresponding visual visible and the other one hidden.