Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have a table with dates and several different columns.
I would like to only include the first x dates, the middle x dates and the last x dates in that table.
Is it possible to add slicers or filters on the page to achieve this?
It needs to be user input friendly to select the range of the three different time periods.
Thanks.
Solved! Go to Solution.
Hi @Anonymous ,
I'm afraid it can't be realized just by filter or slicer as you wanna present 3 different periods of time,you need to create a new table instead,and the dax expression is as below:
Table 2 =
VAR k =
ADDCOLUMNS (
'Table',
"filter",
VAR d1 =
DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 1 )
VAR d2 =
DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 2 )
VAR d3 =
DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 3 )
VAR d10 =
DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 10 )
VAR d11 =
DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 11 )
VAR d12 =
DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 12 )
VAR e1 =
CALCULATE (
MAX ( 'Table'[Date] ),
FILTER (
'Table',
[Date].[Year] = EARLIER ( [Date].[Year] )
&& [Date].[Month] = EARLIER ( [Date].[Month] )
)
)
VAR e11 = e1 - 1
VAR e12 = e1 - 2
VAR e13 = e1 - 3
VAR t = { d1, d2, d3, d10, d11, d12, e1, e12, e13 }
RETURN
IF ( [Date] IN t, 1, 0 )
)
RETURN
SELECTCOLUMNS (
FILTER ( k, [filter] = 1 ),
"Date", [Date],
"City", [City],
"Vist", [Visitor]
)
Then you will see:
Best Regards,
Kelly
Hi @Anonymous ,
Do you wanna show as below?
If so,you only need to edit the interactions between the slicer and the table, if you dont want the table be filtered by the slicer,just click the slicer and and choose the interaction as below:
Best Regards,
Kelly
Looking for something like this:
Full table:
Date | Value |
1/1/2020 | 14 |
1/2/2020 | 2 |
1/3/2020 | 12 |
1/4/2020 | 14 |
1/5/2020 | 17 |
1/6/2020 | 16 |
1/7/2020 | 2 |
1/8/2020 | 6 |
1/9/2020 | 9 |
1/10/2020 | 14 |
1/11/2020 | 17 |
1/12/2020 | 18 |
1/13/2020 | 20 |
1/14/2020 | 9 |
1/15/2020 | 3 |
1/16/2020 | 18 |
1/17/2020 | 13 |
1/18/2020 | 1 |
1/19/2020 | 12 |
1/20/2020 | 16 |
1/21/2020 | 11 |
1/22/2020 | 15 |
1/23/2020 | 13 |
1/24/2020 | 15 |
1/25/2020 | 5 |
1/26/2020 | 1 |
1/27/2020 | 20 |
1/28/2020 | 7 |
1/29/2020 | 12 |
1/30/2020 | 12 |
Then I would like the option to filter for several specific date intervals such as this output:
Date | Value |
1/1/2020 | 14 |
1/2/2020 | 2 |
1/3/2020 | 12 |
1/10/2020 | 14 |
1/11/2020 | 17 |
1/12/2020 | 18 |
1/28/2020 | 7 |
1/29/2020 | 12 |
1/30/2020 | 12 |
Hi @Anonymous ,
I'm afraid it can't be realized just by filter or slicer as you wanna present 3 different periods of time,you need to create a new table instead,and the dax expression is as below:
Table 2 =
VAR k =
ADDCOLUMNS (
'Table',
"filter",
VAR d1 =
DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 1 )
VAR d2 =
DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 2 )
VAR d3 =
DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 3 )
VAR d10 =
DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 10 )
VAR d11 =
DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 11 )
VAR d12 =
DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 12 )
VAR e1 =
CALCULATE (
MAX ( 'Table'[Date] ),
FILTER (
'Table',
[Date].[Year] = EARLIER ( [Date].[Year] )
&& [Date].[Month] = EARLIER ( [Date].[Month] )
)
)
VAR e11 = e1 - 1
VAR e12 = e1 - 2
VAR e13 = e1 - 3
VAR t = { d1, d2, d3, d10, d11, d12, e1, e12, e13 }
RETURN
IF ( [Date] IN t, 1, 0 )
)
RETURN
SELECTCOLUMNS (
FILTER ( k, [filter] = 1 ),
"Date", [Date],
"City", [City],
"Vist", [Visitor]
)
Then you will see:
Best Regards,
Kelly
It's something similar to this I'm looking for, but I would like to have it all combined in one table, not three seperate tables.
Hi @Anonymous ,
Can you show me your expected result?
Best Regards,
Kelly
I would start by creating a dedicated date table to offload the heavy lifting from your main data table. Create a relationship between the date column and a column of contiguous dates in the date table. Then create other columns as labels to work as filters. I don't know the specifics of the behavior you want but you could use a date diff column that labels each row with how many days ago it is, then have a number slicer to select a range of those for instance. Or if you want to have set blocks of dates (for instance if you want a button that always selects the last 30 days) you could have a column that labels all those dates with "Last 30 Days" and use that as a slicer.
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |