cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Trojden
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 totalnumber of visit to the vet
Pinky3019810
Milky451795
Swifty61122

 

Scenario 3: I press Date on the slicer:

Datetotal shenaniagns mademeals eaten totalnumber of visit to the vet
1/1/2020480
1/2/2020581

 

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
amitchandak
Super User
Super User

@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.

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:

sturlaws_0-1596568126363.png

 

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.

 

lbendlin
Super User
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.

Helpful resources

Announcements
Carousel_PBI_Wave1

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.

Power BI Summit Carousel 2

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!

BizApps LATAM 2023

Business Application LATAM Summit 2023

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

Power Platform Bootcamp

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