topic Re: Selectedvalue and Date-Slicer Issue in DAX Commands and Tips

Selectedvalue and Date-Slicer Issue

Anonymous — Fri, 24 Mar 2023 12:34:37 GMT

Hello,
I have a slicer that contains a date hierarchy. Additionally, I have a table visual.
The table visual is set to have no filter actions when the slicer is selected (using the edit interactions option).
However, in my table visual I want to show data for the previous month of the selected value in the slicer.
The slicer date values come from a separated date table (tbl_dates).

Using the following measure I would like to get the data from the master_actuals table for the previous month of my selection.
Happy for any help.

Measure =

Var Selected_Date = SELECTEDVALUE(tbl_dates[Date])

Var Previous_Month_Date = EOMONTH(Selected_Date,-1)

Var Prev_Month_Selected = MONTH(Previous_Month_Date)

Var Year_Selected = Year(Previous_Month_Date)

Var Result =

CALCULATE(SUM(master_actuals[Amount]),

ALL(master_actuals),

master_acutals[Month] = Prev_Month_Selected,

master_acutals[Year] = Year_Selected

)

Return Result

When I add the month and year manually it works just fine:

master_acutals[Month] = 1,

master_acutals[Year] = 2023

Re: Selectedvalue and Date-Slicer Issue

johnt75 — Fri, 24 Mar 2023 12:37:14 GMT

Make sure that there is no relationship between your dates table and the master fact table, then turn interactions between the visuals. That way no filter will be applied to the fact table, but you can still use SELECTEDVALUE and your existing code should work.

Re: Selectedvalue and Date-Slicer Issue

Anonymous — Fri, 24 Mar 2023 12:54:21 GMT

Thanks for the response.
I would like to get it work even with a relationship between the two tables as it is a convenient filter for other visuals based on a date.

Re: Selectedvalue and Date-Slicer Issue

johnt75 — Fri, 24 Mar 2023 13:53:02 GMT

You will need to enable interaction between the visuals, otherwise the selected value from the slicer will never be passed into the target visual. If the relationship needs to be present for other visuals then you will need to modify any measures used in the target visual, possibly by using a calculation group, to disable the relationship or otherwise override it.