Forum Discussion
Implementing -Select with between on Model - Based on Slicer Date Selection
***Need help with how to pass Date based on Slicer calculated on a date table at the report level to calculate the new table at the model level .***
I am trying to use DAX:
To derive a table from an existing table (DimMembers) with all columns.
Where the date selected in a slicer is falling between DimMembers.StDate and DimMembers,EndDate.
MemberID | Name | Area | StDate | EndDate |
2 | Ram | AZ | 05-01-2019 | 15-01-2019 |
2 | Ram | CA | 16-01-2019 | 27-01-2019 |
2 | Ram | LA | 28-01-2019 | 31-12-2099 |
3 | Shyam | CA | 02-01-2019 | 16-01-2019 |
3 | Shyam | AZ | 17-01-2019 | 25-01-2019 |
3 | Shyam | LA | 26-01-2019 | 31-12-2099 |
Tab 2: sampleDate
asofdate |
25-12-2018 |
31-01-2019 |
For Example if the slicer date is 31-01-2019 then, resultant derived table should have:
Name | Area | StDate | EndDate |
Ram | LA | 28-01-2019 | 31-12-2099 |
Shyam | LA | 26-01-2019 | 31-12-2099 |
--------
Option Tried:
- MAXASOFDATE is calculated with reort level filter.
- However, in the current method, we were always getting the max pre-calculated irrespective of the filters at the report level.
- Tried creating a new derived table asofmaxTable = ADDCOLUMNS(sampleDate,"maxDate",max(sampleDate[asofdate])) and used it in the filter clause as below and that did not help.
- It is still ignoring the filter i have now on "table asofmaxTable " at the report level.
1 Reply
- sturlaws
Resident Rockstar
Hi Anonymous
calculated tables (and columns) can not be updated by slicers, they are populated at query time(when data is loaded into the model).
There is the option of parameters, but that might be of limited use in your case:
https://www.youtube.com/watch?v=iiNDq2VrZPYhttps://www.youtube.com/watch?v=twBUmqVOGgg
Cheers,
Sturla
