Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi,
I have a report which connects to ADX data source through DirectQuery. We have data from 1950s. So, i guess my report will give performance issues with such a huge data(as the load method is DirectQuery). I want users to access the whole historic data but not all at a time. So they want to see any 10 years data at a time.
Is there any way to have the date slicer in a "between " format and at the same time restrict the user to select only the dates before 10 years from the start date.?
Example:
If the user selects 01/01/1950 in the start date,the end date should show dates only upto 01/01/1960
If the user selects 01/01/2010 in the start date,the end date should show dates only upto 01/01/2020
Please help me.
Thanks,
Sai Priya.
Solved! Go to Solution.
Hi @SaiPriyaEleti ,
You need a data table and a date table ,they with no relationship between them.If you use a date in the data table as a slicer, then when you pick the value in the slicer, only one data for that date will be returned, so you need another date table.
Refer:
data_table = CALENDAR("1900,1,1","2021,1,1")
date_table = CALENDAR("1900,1,1","2021,1,1")
Then use the below measure:
TEST =
IF (
MAX ( 'data_table'[Date] )
>= CALCULATE ( MIN ( 'date_table'[Date] ), ALLSELECTED ( 'date_table' ) )
&& MAX ( 'data_table'[Date] )
<= DATE ( YEAR ( CALCULATE ( MIN ( 'date_table'[Date] ), ALLSELECTED ( 'date_table' ) ) ) + 10, MONTH ( CALCULATE ( MIN ( 'date_table'[Date] ), ALLSELECTED ( 'date_table' ) ) ), DAY ( CALCULATE ( MIN ( 'date_table'[Date] ), ALLSELECTED ( 'date_table' ) ) ) ),
1,
BLANK ()
)
Apply measure on filter:
Output:
When I choose slicer:
Output maxdatevalue:
And when I choose 1922,the data table show the max date value is 1392:
You could download my pbix file if you need!
Did I answer your question? Mark my post as a solution!
Best Regards
Lucien
Hi @SaiPriyaEleti ,
You need a data table and a date table ,they with no relationship between them.If you use a date in the data table as a slicer, then when you pick the value in the slicer, only one data for that date will be returned, so you need another date table.
Refer:
data_table = CALENDAR("1900,1,1","2021,1,1")
date_table = CALENDAR("1900,1,1","2021,1,1")
Then use the below measure:
TEST =
IF (
MAX ( 'data_table'[Date] )
>= CALCULATE ( MIN ( 'date_table'[Date] ), ALLSELECTED ( 'date_table' ) )
&& MAX ( 'data_table'[Date] )
<= DATE ( YEAR ( CALCULATE ( MIN ( 'date_table'[Date] ), ALLSELECTED ( 'date_table' ) ) ) + 10, MONTH ( CALCULATE ( MIN ( 'date_table'[Date] ), ALLSELECTED ( 'date_table' ) ) ), DAY ( CALCULATE ( MIN ( 'date_table'[Date] ), ALLSELECTED ( 'date_table' ) ) ) ),
1,
BLANK ()
)
Apply measure on filter:
Output:
When I choose slicer:
Output maxdatevalue:
And when I choose 1922,the data table show the max date value is 1392:
You could download my pbix file if you need!
Did I answer your question? Mark my post as a solution!
Best Regards
Lucien
Thanks Jaipal, I got the answer.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
90 | |
89 | |
79 | |
70 | |
68 |
User | Count |
---|---|
226 | |
129 | |
120 | |
84 | |
78 |