Forum Discussion
Calculated Column/Table Change Dynamically According to Slicer Selection in the Report
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?
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
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 :)