Forum Discussion
Unable to use selectedvalue variable in a calculated table
Hi, I am trying to compare different snapshots in a table by allowing users to select the dates on two different filters. but I got stucked on the first step. when I use SELECTEDVALUE as a variable and passes the variable to the calculated table as a filter , the selected value does not affect the calculated table. This is the code
First_Table =
VAR CurrentSnap =
SELECTEDVALUE(Snapshoptdate[Dw_snapshotdate])
VAR TableA =
CALCULATETABLE(
SELECTCOLUMNS(
'R_Yearly_Summary_Measures',
"UCode", 'R_Yearly_Summary_Measures'[Ucode]
),
'R_Yearly_Summary_Measures'[Dw_snapshotdate] = CurrentSnap
)
RETURN
TableAbut if i pass the alternative like max(snapshotdate) it takes the value and the tables renders, the issues with this is if another value is selected on the filter it does not affect the table. please is there a limitation to this approach, or what function am i using wrongly. thanks
SELECTEDVALUE works in measures and visuals, where the context is dynamically applied by slicers or filters in the report. However, calculated tables are static after being created, and they are not updated dynamically when slicers are applied. Hence, using SELECTEDVALUE in a calculated table won't update the table in response to slicers.
> If you want the table to update dynamically based on the user's selection of Dw_snapshotdate from a slicer, you must create a measure rather than a calculated table.
Create a measure to filter dynamically:
Filtered_UCode_Measure =
VAR CurrentSnap = SELECTEDVALUE(Snapshoptdate[Dw_snapshotdate])
RETURN
CALCULATE(
SELECTCOLUMNS(
'R_Yearly_Summary_Measures',
"UCode", 'R_Yearly_Summary_Measures'[Ucode]
),
'R_Yearly_Summary_Measures'[Dw_snapshotdate] = CurrentSnap
)💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn
6 Replies
SELECTEDVALUE works in measures and visuals, where the context is dynamically applied by slicers or filters in the report. However, calculated tables are static after being created, and they are not updated dynamically when slicers are applied. Hence, using SELECTEDVALUE in a calculated table won't update the table in response to slicers.
> If you want the table to update dynamically based on the user's selection of Dw_snapshotdate from a slicer, you must create a measure rather than a calculated table.
Create a measure to filter dynamically:
Filtered_UCode_Measure =
VAR CurrentSnap = SELECTEDVALUE(Snapshoptdate[Dw_snapshotdate])
RETURN
CALCULATE(
SELECTCOLUMNS(
'R_Yearly_Summary_Measures',
"UCode", 'R_Yearly_Summary_Measures'[Ucode]
),
'R_Yearly_Summary_Measures'[Dw_snapshotdate] = CurrentSnap
)💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedInmdgene , Try using below mentioned DAX
First_Table =
VAR CurrentSnap =
IF(
ISBLANK(SELECTEDVALUE(Snapshoptdate[Dw_snapshotdate])),
MAX(Snapshoptdate[Dw_snapshotdate]),
SELECTEDVALUE(Snapshoptdate[Dw_snapshotdate])
)VAR TableA =
CALCULATETABLE(
SELECTCOLUMNS(
'R_Yearly_Summary_Measures',
"UCode", 'R_Yearly_Summary_Measures'[Ucode]
),
'R_Yearly_Summary_Measures'[Dw_snapshotdate] = CurrentSnap
)
RETURN
TableAbhanu_gautam i tried it, but it only returns the maxdate and does not respond to the filter selection
You can try:
First_Table =
VAR SelectedDates = VALUES(Snapshoptdate[Dw_snapshotdate]) // Get all selected dates
RETURN
CALCULATETABLE(
SELECTCOLUMNS(
'R_Yearly_Summary_Measures',
"UCode", 'R_Yearly_Summary_Measures'[Ucode]
),
'R_Yearly_Summary_Measures'[Dw_snapshotdate] IN SelectedDates
)Kedar_Pande the error is 'A table of multiple values was supplied where a single value was expected.' also this does not give the option of selecting the value from a filter
