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.
Hello,
I need to filter some data with a refence date (which is entered by the user) using 2 dates (start date & end date) that define a validity period.
My table contains :
I want to select a date (idealy by pick in a calendar object) and filter the line where :
[Start Date] < "Selected Date" < [End Date]
for example :
if i pick 20/12/2022, i will filter this line
Also, i have others tables where i want to reproduce this feature, so i would like to use only one date (from my calendar table ?) to drive all the filters on validity periods.
Can someone please help me ?
Thanks.
Nicolas
Solved! Go to Solution.
You could use SWITCH to determine which table is applicable:
Visual Filter =
VAR vSelectedDate =
SELECTEDVALUE ( 'Calendar'[Date] )
VAR vResult =
SWITCH (
TRUE,
ISINSCOPE ( FactTable[Start Date] ),
SUMX (
FactTable,
IF (
vSelectedDate > FactTable[Start Date]
&& vSelectedDate < FactTable[End Date],
1
)
),
ISINSCOPE ( FactTable2[Start Date] ),
SUMX (
FactTable2,
IF (
vSelectedDate > FactTable2[Start Date]
&& vSelectedDate < FactTable2[End Date],
1
)
)
)
RETURN
vResult
Proud to be a Super User!
Create the measure below and use it as a visual filter ("is greater than 0"). The Calendar table does not have a relationship with the fact table. The date slicer uses the Calendar table.
Visual Filter =
VAR vSelectedDate =
SELECTEDVALUE ( 'Calendar'[Date] )
VAR vResult =
SUMX (
FactTable,
IF ( vSelectedDate > FactTable[Start Date] && vSelectedDate < FactTable[End Date], 1 )
)
RETURN
vResult
Proud to be a Super User!
Thanks for your answer, it's a great step for me 🙂
So if i understant well, i have to create a measure to filter each table with validity periods ?
And is there a mean to drive all these filters with only one "general filter" ?
Because if i've got 20 tables with validity periods, when i want to cross all the data in one report table, i 'd prefer use 1 filter rather than 20...
Thanks
Nicolas
You could use SWITCH to determine which table is applicable:
Visual Filter =
VAR vSelectedDate =
SELECTEDVALUE ( 'Calendar'[Date] )
VAR vResult =
SWITCH (
TRUE,
ISINSCOPE ( FactTable[Start Date] ),
SUMX (
FactTable,
IF (
vSelectedDate > FactTable[Start Date]
&& vSelectedDate < FactTable[End Date],
1
)
),
ISINSCOPE ( FactTable2[Start Date] ),
SUMX (
FactTable2,
IF (
vSelectedDate > FactTable2[Start Date]
&& vSelectedDate < FactTable2[End Date],
1
)
)
)
RETURN
vResult
Proud to be a Super User!
I think i can do with that
Thanks for the help
User | Count |
---|---|
87 | |
84 | |
70 | |
62 | |
56 |
User | Count |
---|---|
137 | |
110 | |
92 | |
84 | |
69 |