Have a measure populate a table column to be used in a filter

[G_Whit-UK]

Hi,

I am trying to populate a column in an existing table based on the value of a measure.  The measure has two values which are defined by the user through a slicer ("Month on Month" or "Year on "Year").  The aim is for the slicer to modify the filter criteria of a chart to flip between a month on month view vs a year on year view.

I was trying to have the value of the measure populate a column in the date table, which would then allow me to apply a filter on the selected parameter.

My Data is as follows:

Table 1: "YoY vs MoM" (This is used to populate the slicer for the user to select the required period)

Column Header	YoY / MoM
Row 1	Year on Year
Row 2	Month on Month

Measure:

MoM / YoY = SELECTEDVALUE('YoY vs MoM'[YoY / MoM],"Error")

Table 2:  This table holds my dates and where I want the measure to populate the final column

Column Header	Date (dd/mm/yy)	MoM Marker	YoY Marker	Merged Markers	Output from "MoM / YoY" Measure
Row 1	31/08/20		YoY	Same Month Prior Year
Row 2	30/09/20
...
Row 12	31/07/21	MoM		Prior Month
Row 13	31/08/21	MoM	YoY	Current Month

The goal is to have the final column values adjust based on the user's selection, which in turn will adjust the filters applied to the page. Examples...

Example 1: MoM selected			Example 2: YoY Selected
Merged Markers	Output from "MoM / YoY" Measure		Merged Markers	Output from "MoM / YoY" Measure
Same Month Prior Year			Same Month Prior Year	YoY
Prior Month	MoM		Prior Month
Current Month	MoM		Current Month	YoY

Therefore by setting the filter to the above column and not selecting blanks, the dates should adjust based on the user's selection.

Is there a smarter / better way to achieve the desired impact on the displayed chart?

Thanks

[v-rongtiep-msft · Accepted solution]

Hi @Username,

I  agree with @amitchandak ‘s point. Columns do not take slicer values.

You will need to create a measure instead of a column.
Here's the sample data: YOY as sum and MOM as average.

You will need to create a table as below and use it as slicer.

Then create a measure to switch between YOY and MOM.
measure

Measure = IF(SELECTEDVALUE('Table (2)'[Column1])=1,[YoY],IF(SELECTEDVALUE('Table (2)'[Column1])=2,[MoM],"Error"))

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

Best Regards

Community Support Team _ Polly

[G_Whit-UK]

Thanks @v-rongtiep-msft & @v-stephen-msft , however I'm not sure this truly solves what I am trying to achieve.  What I am trying to achieve is to have a waterfall chart flip between Month on Month (current vs prior month), Year on Year comparison (current month vs same month prior year), or YTD (current year to date vs prior year's year to date) based on a filter where the user can select which comparison they prefer.

Here is a simplified version of a data table (with some months missing to keep this post succinct:

Therefore, if the user selects MoM, then the data for the current month (Sep '21) will be compared vs Aug '21, wheras if they selected YoY the chart will be comparing the current month vs Sep '20.

If the user selected YTD, then it would be the '21 YTD sum (Jan - Sep '21) vs the '20 YTD sum for the same period (i.e. Jan '20 - Sep '20).

Would the proposed solution meet the above requirements?

[amitchandak]

@G_Whit-UK , if you trying to create a new calculated column with help from measure and slicer, that is not possible. Columns do not take slicer values.

You have to create measure in such a case