Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

According to this article, Calculated Column/Table Change Dynamically According to Slicer Selection in the Reportit 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!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

10 REPLIES 10
josephlim
Frequent Visitor

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.

Anonymous
Not applicable

@josephlim - 

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

@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

Anonymous
Not applicable

@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

 

@Anonymous 
Works perfectly.


Thank you very much.  Learnt something new today 🙂

Thejeswar
Resident Rockstar
Resident Rockstar

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

Anonymous
Not applicable

@Thejeswar, Thanks for the response!


Can you give an example of a dynamic calculated column? 

Anonymous
Not applicable

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

Anonymous
Not applicable

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?

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.