Dynamic column with content switchable by slicer

[Trojden]

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?

[amitchandak]

@Trojden , refer if this can help

https://www.youtube.com/watch?v=6jeSIRpjv0M

[Trojden]

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

[sturlaws]

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.

[lbendlin]

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.