Forum Discussion
Dynamic Calculated Column as Visual dimension
My understanding is that Power BI calculated columns are calculated at workbook opening time and can't be modified afterward. Measures are the only way to get dynamic calculations (I suppose there's also parameter fields giving some flexibility to switch which calculated columns are used based on the user's selection). I am coming from tableau where dynamic calculated columns are supported. Can someone please tell me what would be the Power BI recommended approach to the problem below:
I have a sales table. I want to make a chart breaking out many measures based on whether price is >= or < user input X, which is allowed to vary. I know I can make 2 versions of each measure I care about, one scanning rows where price is >= X and another where price < X. However, this doesn't get me the visual I want and I need to make tons of unecessary measures. I know there are calculation groups to make these measures but they require an external tool and also this just seems like a burden coming from Tableau. Is it possible to make a chart like below using another approach besides what I just described?
User input price X:
| Sales Price | measure 1 | measure 2 | measure2 |
| >= X | 50 | 90 | 11 |
| < x | 70 | 30 | 10 |
Hi kahn_ohara
You are correct. Calculated columns in Power BI cannot be dynamic according to user input in the report. Only measures and what-if parameters can be dynamic. However, measures and what-if parameters cannot be used as visual dimension fields. They can only be used as value fields. So you need a table column as a dimension field, which can provide segmentation/filter for other measures.
For example, I add a Group table which only has values 1 and 2. These two values will be used in other measures to provide different calculations for two groups. To show ">=X" and "<X" values, I use a measure and a what-if parameter.
So the result is similar to below.
The problem is that the Group column cannot be removed from the table visual. It should be there to act as a dimension field because all other measures cannot do that job. I have attached a sample file at bottom. Hope this would be helpful.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
4 Replies
kahn_ohara, Are you looking for dynamic Segmentation
Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1387187#M626
Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-kamitchandak Thank you so much. Your video has really helped me resolve the issue.
Hi kahn_ohara
You are correct. Calculated columns in Power BI cannot be dynamic according to user input in the report. Only measures and what-if parameters can be dynamic. However, measures and what-if parameters cannot be used as visual dimension fields. They can only be used as value fields. So you need a table column as a dimension field, which can provide segmentation/filter for other measures.
For example, I add a Group table which only has values 1 and 2. These two values will be used in other measures to provide different calculations for two groups. To show ">=X" and "<X" values, I use a measure and a what-if parameter.
So the result is similar to below.
The problem is that the Group column cannot be removed from the table visual. It should be there to act as a dimension field because all other measures cannot do that job. I have attached a sample file at bottom. Hope this would be helpful.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.Thank you!
I will say that coming from a Tableau or Sql background this seems like a fairly big limitation, but the solution you shared defniitely will get the job done and that is good. I will just note to future readers that as of 5/3/2023 the what if parameter can only accept a finite range of values and can't have more than 1002 values. See below.
So for example, a parameter with range from -20 to 20 with increments of .01 is impossible because this would be 40 x 100 = 4,000 individual values > 1002 values. The fact that you also can't just have an arbitrary floating number parameter with no limits also seems quite wonky to me.
