Forum Discussion
Calculated column based on slicer value
I have been searching for hours, but can't find the solution.
I wish to create a calculated column, based on a selected date in a slicer.
Most solutions use a MIN or MAX function (in my case so far I used this:
Hi, Anonymous ;
Slicers cannot be used in calculated columns. They can, however, be used in measures.
Slicers are Filters, and thus use filter context. Filters can change dynamically based on what is selected, and how filter interactions are defined. Since measures are dynamic fields calculated on demand, they can leverage this filter context to shape results.
Then we get to calculated columns. Calculated columns use row context. They are populated once at data refresh, and don't update again. If you change interactions, change slicers, the underlying row context and any calculations performed at load would be unaffected.So, you should use a measure instead of the calculated column if you would like to change the calculation in a table when the slicer value changed. as follows:
1.create another Date table.
Date = VALUES('Periode'[Date])2.create a measure.
Date_selected = CALCULATE(MAX('Date'[Date]),ALLSELECTED('Date'))#mnd = DATEDIFF(MAX([Startdatum]),[Date_selected],MONTH)The final output is shown below:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
Hi, Anonymous ;
Slicers cannot be used in calculated columns. They can, however, be used in measures.
Slicers are Filters, and thus use filter context. Filters can change dynamically based on what is selected, and how filter interactions are defined. Since measures are dynamic fields calculated on demand, they can leverage this filter context to shape results.
Then we get to calculated columns. Calculated columns use row context. They are populated once at data refresh, and don't update again. If you change interactions, change slicers, the underlying row context and any calculations performed at load would be unaffected.So, you should use a measure instead of the calculated column if you would like to change the calculation in a table when the slicer value changed. as follows:
1.create another Date table.
Date = VALUES('Periode'[Date])2.create a measure.
Date_selected = CALCULATE(MAX('Date'[Date]),ALLSELECTED('Date'))#mnd = DATEDIFF(MAX([Startdatum]),[Date_selected],MONTH)The final output is shown below:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.You can't make a calculated column be affected by slicers, calculated columns are only calculated during data load and refresh. If you need to take account of slicers and filters you need to use a measure.
If you have a slicer applied to the Periode table then MAX(Periode[Date]) should do it.
I am trying to do something similar in terms of adding a calculated column to a data table. I'd like the column to be calculated using all the filters active on the page. I understand that if I change the filter setting, it wont' affect the calculated column until I refresh the page. However, it would allow me to "freeze" a computation based on current filter settings, and use the frozen column in other measures on the page. It would be ideal if the calculate columns could also be built using slicer selections on the page. Thanks
