Forum Discussion
Filtering a column with "SELECTEDVALUE"
Hello everyone,
I am trying to create a filter that will allow me to display a range of fiscal weeks based on a slicer selection.
As you can see, I have that bar chart where it gets values from Fiscal Week 15 to Fiscal Week 52. What I am trying to achieve is when Fiscal Week 25 (or any other week) is selected in the slicer to display only 5 Fiscal Weeks, starting from Fiscal Week selected in the slicer.
There is an active relationtip between the slicer and the bar chart values. So the slicer is filtering the bar chart ( if needed). The Fiscal Week field is column (whole number data type).
I don't want to create a slicer that filters by range, I want to keep the slicer as dropdown but include the filter metioned above in the bar chart.
This is the calculated column I am using as a filter but it doesn't do anything:
Hi Anonymous
Slicer selections do not affect the value of calcualted columns as they update only when:
the underlying data has is refreshed or modified or the calculated column formula is modified.
In order to show the past x weeks based on a slicer selection, you must use a disconnected. Although a measure can return values outside the selected range, the visible rows will be based on what's been selected so if you select Week, you will see only that week.
In the example below, I am returning the value for the last 5 weeks starting from the currently selection. If I used a related table, I would be seeing only week 9.
Please see attached sample pbix.
4 Replies
Hi Anonymous
Slicer selections do not affect the value of calcualted columns as they update only when:
the underlying data has is refreshed or modified or the calculated column formula is modified.
In order to show the past x weeks based on a slicer selection, you must use a disconnected. Although a measure can return values outside the selected range, the visible rows will be based on what's been selected so if you select Week, you will see only that week.
In the example below, I am returning the value for the last 5 weeks starting from the currently selection. If I used a related table, I would be seeing only week 9.
Please see attached sample pbix.
A calculated column is only calculated during data refresh so it doesn't pay any attention to slicers or filters.
Have a look at https://www.sqlbi.com/articles/show-previous-6-months-of-data-from-single-slicer-selection/ . In that they are using months but you can easily adapt it to use fiscal weeks.
Hey folks,
A quick tip: calculated columns don’t react to slicers only measures do, because they’re evaluated based on what the user selects. To show 5 weeks starting from the slicer’s selected week:
-
Create a measure (not a column):Show5Weeks =
VAR StartWeek = SELECTEDVALUE('Fiscal Year'[FISCAL_WEEK])
RETURN
IF(
'Fiscal Year'[FISCAL_WEEK] >= StartWeek &&
'Fiscal Year'[FISCAL_WEEK] < StartWeek + 5,
1, 0
) -
Drop this measure into your visual’s Filters pane and set it to show items when the value is 1.
-
Keep your slicer as a dropdown the chart will always show the selected week plus the next four.
Thank you for your fast reply.
This doesn't work because Fiscal Week is a column. So it needs to be aggregated to work.
If I edit your solution to look like this:
Selected_Weeks_Filter =
VAR SelectedWeek = SELECTEDVALUE('Fiscal Year'[FISCAL_WEEK])
RETURN
IF(
MIN('Fiscal Year'[FISCAL_WEEK]) >= SelectedWeek && MAX('Fiscal Year'[FISCAL_WEEK]) < SelectedWeek + 5,1,0)
It still doesn't work.
-
