Create a Calculated Column Based on a Slicer

[WelterM]

I have a Tree Map that displays Region, colored by a measure. What I would like is to use a Slicer to determine the dimension used for the Tree Map. Here's what I have so far.

I created a static table with one column and four values: Region, Subregion, Territory, Account

I created a slicer based on that column.

Now, I need to create a calculated column that will display the correct column values based on the slicer selection. If the user selects Region, that column will display the values in the Region column from the Account table. If they choose Subregion, the column will display the values in the Subregion column, and so on. Then I'll use the calculated column for the Tree Map, instead of the Region column.

Is this possible? If so, how?

[v-piga-msft]

Hi @WelterM,

Not like measures, calculate columns are computed during database processing(e.g. data refresh) and then stored in the model, they do not response to user selections on the report.

So it is not possible to create a calculate column/table can change dynamically with user selections on the report.

For the workaround, you could try to unpivot the columns: Region, Subregion, Territory, Account, then you will get two columns like Attribute and Values. In this scenario, you can have a Slicer on the column Attribute.

Visuals like this:

For more details, you could refer to my test pbix.file.

Best Regards,

Cherry  

[WelterM]

Thanks, Cherry. How do I unpivit those four columns? The table that contains them has almost 200 columns.

[v-piga-msft]

Hi @WelterM,

To unpivot the columns, you could follow the steps below:

1.Click Edit Queries ——Transform

2. In your case, you could choose the four columns you want to unpivot.

3. Click Unpivot columns and it will create two new column Attribute and Value.

By my test, when you unpivot the columns, the other columns will not affect the columns you have unpivot.

Do you have a try to unpivot the columns? Is there any errors? If you have some problems, could you share your dummy pbix file which can reproduce the scenario? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)

Hope it can help you!

Best regards,

Cherry