***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:
In the reporting layer we add a filer on our sampleDate table
Calculate the max(asofdate) in a variable
Derive the tabe as derivedTable = (VAR ASOFDATEMAX=MAX(asofmaxTable[asofdate]) RETURN CALCULATETABLE(DimMembers,FILTER(DimMembers,DimMembers[StDate]>ASOFDATEMAX)))
Expectation Vs Actual Behaviour:
- 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.
derivedTable = (VAR ASOFDATEMAX=MAX(asofmaxTable[asofdate])
RETURN CALCULATETABLE(DimMembers,FILTER(DimMembers,ASOFDATEMAX<DimMembers[StDate])))
- It is still ignoring the filter i have now on "table asofmaxTable " at the report level.
Need help with how to pass max of asofdate based on filters on the date table at the report level to calculate the new table at the model level .
