cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
WelterM Helper I
Helper I

Create a Calculated Column Based on a Slicer

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?

3 REPLIES 3
v-piga-msft Resident Rockstar
Resident Rockstar

Re: Create a Calculated Column Based on a Slicer

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:

Capture.PNG

 

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

 

Best Regards,

Cherry  

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
WelterM Helper I
Helper I

Re: Create a Calculated Column Based on a Slicer

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

v-piga-msft Resident Rockstar
Resident Rockstar

Re: Create a Calculated Column Based on a Slicer

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

Top Solution Authors
Top Kudoed Authors