Do I need auxiliary functions (Max(), Sum(),...) to apply a measure on a column?

[Eddy18]

Hi all,

i came across the same problem now for several times and I am interested if there is a simple solution that I just dont know.

Consider the toy-example:

I have a table with columns y and x, where x = 01.01.2024, 01.02.2024, 01.03.2024. y = 5, 1, 56. That is, i got 3 dates and corresponding values (numbers). I aim to get a line graph in power BI where the user can choose a parameter p (via dropdown in a slicer) and the diagram shows the values of p*y over x. That is, if the user sets the parameter p as 2, the diagram shows the values 10,2,112. 

This can be done using a measure on the column y and the selectedvalue function using the following logic:

Values = SELECTEDVALUE(PARAMTABLE[param])*MAX(DATA[y])

Works fine so far. Since Measures can only be applied to values, not columns, I use an auxiliary function (here, Max(), but could also be done with Sum() etc.). In the line graph, the max function does not do anything since for each datapoint the Max() function is taken over only one line. 

This is obviously a trick and just solves the problem that a measure cannot be applied to the whole column. It works, however, this solution also comes with some drawbacks (expecially if you are interested in time-aggregated values for more complex computations).

My question is: Is this the common solution for this situation or is there a proper approach provided by power BI functionalities (there should be...).

Thanks a lot in advance!

[v-nuoc-msft]

Hi @Eddy18 

@AnalyticsWizard Thank you very much for your prompt reply. Allow me to share some content here.

When more complex calculations are required in your data, max may indeed not be able to fulfill the requirements.

For example, when your date corresponds to more than one "Y":

“DATA”

"ParamTable"

You can create a measure.

Determine if a slicer is selected after summing 'Y' for the same date. If slicer is selected, param * Y, otherwise Y.

Measure = 
VAR _SLICER = SELECTEDVALUE('ParamTable'[param])
VAR _Y = CALCULATE(SUM('DATA'[Y]), FILTER(ALL('DATA'), 'DATA'[X] = MAX('DATA'[X])))
RETURN IF(ISFILTERED('ParamTable'[param]), _SLICER * _Y, _Y)

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Eddy18]

Thanks for your reply!
In fact, this was (and still is) my main problem with this approach: it leads to problems (esp. regarding aggregation) in more complex situations. In your described situation it is still easy to solve this problem (as you described). But let me give you another example:

Assume we have data:

and potential parameter values:

and I am interested in the value Parameter*y^2 plottet over the dates. Using the measure:

Value = SELECTEDVALUE(Param[Parameter])*MAX(Tabelle1[y])^2

gives the desired values when used in a line graph with Parameter = 1:

Now imagine i want to use the date hierarchy in the diagram and consider aggregated (lets say averaged) quarterly values. That is, if I choose Parameter=1, I would like to get averaged values for Q1, i.e. (1*3^2 + 1*2^2 + 1*3^2)/3 = 22/3 and for Q2: (1*1^2 + 1*5^2 + 1*1^2)/3 = 27/3. However, using the drillup in the graph it gives: 

As expected, the max-function is now evaluated on both quartals (leading to the same problem as you decribed: we end up with several y-values for one x-value) and, hence, for it takes the maximum value for Q1 (3) and Q2 (5), takes the square and multiplies it by Parameter (here: 1), leading to quarterly values 9  (for Q1) and 25 (for Q2) instead of 22/3 and 27/3.

Do you have an Idea how to get the desired output in this situation?
I know the described example seems to be artificial but it is in fact the situation that I am looking for.

Looking forward to your answer! Thanks in advance

[v-nuoc-msft · Accepted solution]

Hi @Eddy18 

I'm not sure what kind of results you want to get, but for the calculations you're proposing, here's the method I'm offering:

Create measures.

power Y = 
var _Y = POWER(SELECTEDVALUE('Data'[Y]), 2)
RETURN _Y

RESULT = 
var _parameter = SELECTEDVALUE('Parameter'[Parameter])
var _qtr = QUARTER(SELECTEDVALUE('Data'[X]))
RETURN
(SUMX(FILTER(ALL('Data'), QUARTER('Data'[X]) = _qtr), [power Y]) * _parameter) / 3

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AnalyticsWizard]

@Eddy18 

The approach you've described is commonly used in Power BI to dynamically manipulate chart values based on slicer selections. Using a measure to calculate the result of a parameter (`p`) multiplied by a column (`y`) is standard practice because measures are designed to be dynamic and respond to the filter context, which includes slicers.

However, there are some nuances to consider:

1. Using `MAX` or `SUM` as an auxiliary function will work when there's only one value of `y` per category (or `x` in your case). It's essentially a workaround to ensure the measure returns a value that can be plotted on the y-axis of the line chart.

2. If your table has multiple `y` values for the same `x` and you're interested in aggregation over time, you should explicitly use the appropriate aggregation function (like `SUM`, `AVERAGE`, etc.) depending on the analysis you want.

If you only have one `y` value for each `x` and you are interested in scaling this value by a slicer-selected parameter, your current solution is correct. However, if you want the measure to be more transparent and avoid using an aggregation function as a workaround, you could consider creating a calculated column that directly multiplies `y` by `p`. This isn't dynamic with slicer changes, but it could be useful for static reports.

For a dynamic approach that's more intuitive than using `MAX`, you might simply reference the `y` value directly in your measure, as each `x` should correspond to a unique `y` in the context of a line chart:

Values = SELECTEDVALUE(PARAMTABLE[param]) * DATA[y]

If you use this in a line chart with `x` on the axis, it should correctly calculate `p * y` for each point on the chart since the context of each point is set by the axis.

Finally, Power BI does not allow binding a slicer directly to modify a column because slicers are meant to filter data, not to change it. Measures are the way to go when you need dynamic calculations responding to user interactions.

Remember, when designing a measure for use in a visual, the measure should make sense with the visual's context and should aggregate properly at each level of granularity displayed.

If this post helps, please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

[Eddy18]

Hi @AnalyticsWizard, thanks for your reply confirming that my approach is at least some sort of 'standard' in this situation...
I am a bit confused regarding your proposed alternative solution for a dynamic approach not using Max(): Did you mean to reference the column directly in the measure via:

Values = SELECTEDVALUE(PARAMTABLE[param]) * DATA[y]

This is not possible since you are not allowed to reference a comumn directly in a measure, right?

Thanks in advance!