Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: Calculated Column/Table Change Dynamically Acc...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Anonymous

Not applicable

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Calculated Column/Table Change Dynamically According to Slicer Selection in the Report

10-09-2018
06:21 PM

According to this article, Calculated Column/Table Change Dynamically According to Slicer Selection in the Report, it is not possible to create a calculate column/table that can change dynamically with user selections such as slicers on the report. (For example, using SELECTEDVALUE in the Calculate Table formulas)

Is there an alternative way that I can parameterize my calculated table? How do I execute it?

Thanks!

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-27-2019
08:17 PM

@Anonymous - Calculated Columns are created and stored in the table when the model is refreshed. The value stored in the row won't change according to the user selection.

What are you trying to accomplish?

10 REPLIES 10

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-14-2019
08:44 PM

How about the following scenario:

1. A group of customers needs to be scored using RankX and What-If parameters

2. The RankX measures are used in many other calculations which slows down performance as every measures needs to recalculate the RANKX.

3. Storing the results of the calculation based on slicer selections and What-If Parameters

4. Use the Caculated table for other measures so that each measures uses the RANKX column in the calculated table do not have to reprocess the RANKING again.

5. Also after ranking, we can use the Star Rating but is a measure.

6. We want to show the count of customers by the Star Rating - Means using Star Rating as legend in charts which is not possible now.

Is there an alternative way to store a result table based on all the measures computed so that the result table can be resuse without calculating the results again.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-15-2019
07:07 AM

The power of measures is their flexibility - you don't need to know all of the selected combinations ahead of time. If you want to create a calculated table, you will need to decide which combinations of attributes are relevant.

Semantics: these would not be measures, but rather calculated columns, as you can't "store" measures.

Hope this helps,

Nathan

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-15-2019
07:03 PM

@Anonymous

Thanks for the advise.

This means there are no alternative ways but to compute the measures?

I understand that Calculated Columns will not be affected nor the values will be changed by slicers. Calculated Columns are not interactive.

My issue is based on the data filtered by Slicers, I will rank these result sets using RANKX and What-If parameters.

The Scenario I am trying to build is as follows

1. I need to rank 7 meaures based on selections of 3 slicers and 1 What-If Parameters by Product

2. Convert each rank into percentage using the Rank value / Max rank value

3. Apply formula using What-if parameters values and Rank Percentage

4. Rank the Product again based on the computed value in step 3.

5. Categorize/Bin the Product by values in Step 4. Currently using the Star Rating

6. I want to see the sum of values (e.g. Revenue) by each category/bin in Step 5.

Currently all the above are done using measures. I am not able to Group/Bin the computed value in Step 4 as it is not allowed. Hence I am not able to perform Step 6 using graph/pie charts. I need to recompute indivdually the number of product and revenue for each of the Stars Rating separately (e.g. 5 Stars will have 5 measures for revenue - 1 measures for each star).

Appreciate if you have any idea on the best way to approach the scenario.

Thanks

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-15-2019
08:29 PM

@josephlim - You could have a binning measure, with a SWITCH statement, like:

binning measure = SWITCH( TRUE(), [value] <= 5, "0-5", [value] <=10, "6-10" "11+" )

I think for step 6, you could have a disconnected parameter table with a row for each rating. Then use that rating column in a new measure with a switch statement, like this:

rating measure = SWITCH( SELECTEDVALUE('Rating Table'[Rating], 1, <Calculate the 1 star> 2, <Calculate the 2 star> 3, <Calculate the 3 star> 4, <Calculate the 4 star> <Calculate the 5 star> )

Then in the pie chart, you could add the rating column from the disconnected table, and the rating measure.

Hope this helps,

Nathan

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-15-2019
11:17 PM

@Anonymous

Works perfectly.

Thank you very much. Learnt something new today 🙂

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-10-2018
02:29 AM

Hi @Anonymous,

Yes definitely you can create a parametrized Calculated Column based on SELECTEDVALUE DAX. But I am not sure if it is possible to create a parametrized calculated table using SELECTEDVALUE.

As per my understanding, we will not be able to create a parametrized Calculated table using SELECTEDVALUE DAX..

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-27-2019
08:09 PM

@Thejeswar, Thanks for the response!

Can you give an example of a dynamic calculated column?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-27-2019
08:17 PM

@Anonymous - Calculated Columns are created and stored in the table when the model is refreshed. The value stored in the row won't change according to the user selection.

What are you trying to accomplish?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-27-2019
08:26 PM

Hi @Anonymous,

Thanks for the response! I just want to know if it's possible.

Just to clarify, dynamic selection only works for Calculated Measures and NOT for Calculated Columns and Tables?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-27-2019
09:17 PM

@Anonymous -

Well, it depends on what you mean, exactly. If you filter for the year 2019, you will actually be filtering all of the rows in the related tables. So in that sense, the selected rows are dynamic, so the table and any calculated columns are dynamic.

But, you are correct that a Measure is calculated at query time, while tables and calculated columns are established prior to the query and don't change - only get filtered.