Pass slicer value to a calculate column

[odalistt]

Hello everybody 🙂

I have this doubt: How can I pass the selected value of the slicer to this column called "KPI"?

The KPI column will be a dynamic column because it will always change its value, depending on which option is chosen in the slicer.

For example if I choose the option "1" of the slicer, that "1" should be stored in the column in the KPI column, in such a way that KPI = 1

Thanks!

[TomMartens · Accepted solution]

Hey,

maybe you will find my answer annoyingly long. But just to be clear and provide a little more background, instead of just writing this is not possible 🙂

There are three objects that can be created using DAX:

• A calculated column

• A measure

• A calculated table

The main differences (from my perspective) between a calculated column and a measure are:

The calculated column

• Has an impact on memory consumption, the more distinct values are created inside the parent table the larger the impact on valuable RAM

• Will only be re-calculated if the data model is refreshed, besides this event, the aggregation function that is assigned to numerical columns (by default: SUM) determines how calculated columns will be treated during query execution

• Can be used as content for a slicer and on axis of visuals

The measure

• Has a much much less impact on memory consumption, be aware of table materialization during query execution

• Will be re-calculated whenever a DAX query is issued (of course only when the measure is used inside the query), a DAX query is issued whenever a user interacts with the data model, e.g. changes the selection of a slicer or uses a bar inside a bar chart to "cross filter" the data model

• can't be used inside a slicer and can't be used as an axis of a chart (except with the scatter chart and similar charting types

With the above mentioned it will not be possible to pass a slicer selection to a calculated column, if you want to provide the user with some sort of "input for calculation" mechanism you have to consider to use a measure.

A good start for this, is this article about "What if parameter":

https://community.powerbi.com/t5/Desktop/Pass-slicer-value-to-a-calculate-column/m-p/559470

Hopefully this provides what you are looking for.

Regards,

Tom

[v-piga-msft · Accepted solution]

Hi @odalistt,

TomMartens‘ s reply is really detailed.

In Power BI, measure is dynamic. For your requirement, you could create a measure refer to the formula below.

Measure = SELECTEDVALUE(Table1[Slicer])

If you have solved your problem, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

Best Regards,

Cherry

[TomMartens · Accepted solution]

Hey,

maybe you will find my answer annoyingly long. But just to be clear and provide a little more background, instead of just writing this is not possible 🙂

There are three objects that can be created using DAX:

• A calculated column

• A measure

• A calculated table

The main differences (from my perspective) between a calculated column and a measure are:

The calculated column

• Has an impact on memory consumption, the more distinct values are created inside the parent table the larger the impact on valuable RAM

• Will only be re-calculated if the data model is refreshed, besides this event, the aggregation function that is assigned to numerical columns (by default: SUM) determines how calculated columns will be treated during query execution

• Can be used as content for a slicer and on axis of visuals

The measure

• Has a much much less impact on memory consumption, be aware of table materialization during query execution

• Will be re-calculated whenever a DAX query is issued (of course only when the measure is used inside the query), a DAX query is issued whenever a user interacts with the data model, e.g. changes the selection of a slicer or uses a bar inside a bar chart to "cross filter" the data model

• can't be used inside a slicer and can't be used as an axis of a chart (except with the scatter chart and similar charting types

With the above mentioned it will not be possible to pass a slicer selection to a calculated column, if you want to provide the user with some sort of "input for calculation" mechanism you have to consider to use a measure.

A good start for this, is this article about "What if parameter":

https://community.powerbi.com/t5/Desktop/Pass-slicer-value-to-a-calculate-column/m-p/559470

Hopefully this provides what you are looking for.

Regards,

Tom