cancel
Showing results for
Did you mean:
Frequent Visitor

## Dynamic column with content switchable by slicer

Let's have two groups of animals:

Dogs = (Rex, Azor, Beethoven, Boomer)

Cats = (Pinky, Milky, Swifty)

We create a table with few a measures: total shenaniagns made, meals eaten total, number of visit to the vet.

What I want to achieve is to have slicer with values: Dogs, Cats and Date.

And by this slicer I want to switch contents of the first coulmn on my table:

Scenario 1: I press Dogs on Slicer, it shows:

 Dogs total shenaniagns made meals eaten total number of visit to the vet Rex 23 190 10 Azor 7 199 11 Beethoven 50 220 14 Boomer 19 200 13

Scenario 2: I press Cats on the slicer:

 Cats total shenaniagns made meals eaten total number of visit to the vet Pinky 30 198 10 Milky 45 179 5 Swifty 6 112 2

Scenario 3: I press Date on the slicer:

 Date total shenaniagns made meals eaten total number of visit to the vet 1/1/2020 4 8 0 1/2/2020 5 8 1

I would like to have it in one table, but with dynamic column. It's rows would let me calculate measures for members of this groups.

What I have alresy did is:
1. Created separated table with values for slicer: Dogs, Cats, Date.
2. Tried to create calculated column, but to no avail, it gives me error:

coulmn1 =
VAR Selection =
SELECTEDVALUE ( Switcher[Name )
RETURN
SWITCH (
TRUE (),
Selection = "Dogs", VALUES(Table1[Dogs]),
Selection = "Cats", VALUES(Table2[Cats]),
Selection = "Date", VALUES(Dates[Date]),
"Choose one" )

Any ideas how to make it work? One way or another?

4 REPLIES 4
Super User
Frequent Visitor

@amitchandak Thanks for a link. It was one of a first videos I came across 🙂

Unpivoting data doesnt work for me, because unpivot with dates as a value would seriously bloat my model.

What I have done is to copy the table 3 times, set each copy to be visible per bookmark and used bookmarks to load given set of data. It's workaround, but it works nonetheless.

Super User

Hi @Trojden

I played around with this a little, and came up with a solution. Essentially it is to use Power Query to create a separate table, Type, with all possible values for dogs, cats and dates, and assign them a type(Dog/Cat/Date). This table then has two columns Type and TypeValue(name of the animals, dates).

Then create a measure like this:

``````number of events =
VAR _slicervalue =
CALCULATE ( SELECTEDVALUE ( 'Type'[Type] ) )
VAR _name =
CALCULATE ( SELECTEDVALUE ( 'Type'[TypeValue] ) )
RETURN
SWITCH (
TRUE (),
_slicervalue = "Cat"
|| _slicervalue = "Dog", COUNTROWS (
FILTER ( Events, Events[Cat/Dog] = _slicervalue && Events[Name] = _name )
),
_slicervalue = "date", COUNTROWS ( FILTER ( Events, Events[Date] = VALUE ( _name ) ) )
)
``````

and create a slicer with Type[Type]. Next create a matrix with TypeValue on the rows and Event(meals/vet/shenaningans), and [number of events] as value:

I have attached the .pbix file I created

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

Super User

These are not dynamic columns (thankfully).  What you need to do is create measures for each of these "columns"  (keys, in reality).

The measures are controlled by the slicer selections.

The net- net is that you can't do that with calculated columns, but you can do it with measures. BUT ONLY if the measure keeps its field type.  What you cannot do is show numbers for dogs and text for cats in the same measure, for example.  Again, thankfully that's not what you actually ask for.

BTW, shenanigans are not made. They are performed.

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

#### Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors