Reputation: 723
Visuals based on Dynamic Calculated Columns in Power BI
As of 5/2/2023, 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. There are "parameter fields" giving some flexibility to switch which calculated columns are used based on the user's selection of a parameter. However, I'm interested in how to solve cases where a dynamic calculated column would be the most natural solution. For example, consider the problem below.
Suppose I have a sales table with a column called 'Price'. I want to create a table visual breaking out many measures based on whether Sales price is >= or < X, which is a user entered value. See below for what I mean.
Price = User input X (can be updated dynamically)
| Price Cat | Measure 1 | Measure 2 |
|---|---|---|
| >= x | 25 | 75 |
| < x | 50 | 99 |
In an ideal world I would create a dynamic calculated column in the sales table called price category but this is not supported.
The only solution I am aware of is to make 2 versions of each measure I care about, one filtering the Sales table to rows where price is >= X and another to rows where price < X. This can be done using the Calculate function. Then I could make 4 separate table visuals, arrange them in a 4x4 pattern, hide column headers for the 2 table visuals (3) and (4) below to create the illusion that they're 1 table visual.See below for what I mean.
Measure 1_ge = Calculate(Measure1, Price >= 'Parameter'[col])
Measure 1_lt = Calculate(Measure1, Price < 'Parameter'[col])
Measure 2_ge = Calculate(Measure2, Price >= 'Parameter'[col])
Measure 2_lt = Calculate(Measure2, Price < 'Parameter'[col])
Table1={">= x"}
Table2={"<x"}
Rename the column in Table1 and Table2 to Price Cat
| Price Cat (1) | Measure 1_ge (2) | Measure 2_ge (2) |
|---|---|---|
| >= x | 25 | 75 |
| Price Cat (3) | Measure 1_lt (4) | Measure 2_lt (4) |
|---|---|---|
| < x | 50 | 99 |
However, this seems like a less than ideal approach since it involves a hack of combining 4 separate table visuals and requires the creation of redundant measures. Power Bi wizards, is there a better way?
Upvotes: 0
Views: 513
