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 have a table with this structure:
Name | Start date | End date |
A | 01/02/2020 | 25/02/2021 |
B | 02/06/2020 | 01/01/2021 |
C | 01/05/2020 | |
D | 20/04/2020 | 31/12/2020 |
Date format is dd/mm/yyyy
I need to pick a date from a slicer, and based on that date I must retrieve all the records that were active on that date (picked date between start and end date, both included). Records with empty end date are still active.
Examples:
10/2/2020
I should get record: A
1/1/2021
I should get records: A, B, C
1/5/2020
I should get records: A, C, D
I don't know how to do this.
Solved! Go to Solution.
Hi @Terrassa,
Depending on how you want to list the final output, you may need one measure plus additional filter linked to this measures or two measures below. Check the attached PBIX file for more details.
filterMeasure =
VAR startDate = MAX ( dataTbl[Start date] )
VAR endDate = MAX ( dataTbl[End date] )
VAR selectedDate = MAX ( calendarTbl[Date] )
VAR conditionOne = SWITCH ( TRUE(),
ISBLANK ( startDate ), TRUE(),
startDate <= selectedDate, TRUE(),
FALSE() )
VAR conditionTwo = SWITCH ( TRUE(),
ISBLANK ( endDate ), TRUE(),
endDate >= selectedDate, TRUE(),
FALSE() )
RETURN IF ( conditionOne && conditionTwo, 1, 0 )
listedNames =
VAR _tbl = ADDCOLUMNS ( dataTbl, "flag", [filterMeasure] )
RETURN CONCATENATEX ( FILTER ( _tbl, [flag] = 1 ), [Name], ", " )
Best Regards,
Alexander
Hi @Terrassa,
Depending on how you want to list the final output, you may need one measure plus additional filter linked to this measures or two measures below. Check the attached PBIX file for more details.
filterMeasure =
VAR startDate = MAX ( dataTbl[Start date] )
VAR endDate = MAX ( dataTbl[End date] )
VAR selectedDate = MAX ( calendarTbl[Date] )
VAR conditionOne = SWITCH ( TRUE(),
ISBLANK ( startDate ), TRUE(),
startDate <= selectedDate, TRUE(),
FALSE() )
VAR conditionTwo = SWITCH ( TRUE(),
ISBLANK ( endDate ), TRUE(),
endDate >= selectedDate, TRUE(),
FALSE() )
RETURN IF ( conditionOne && conditionTwo, 1, 0 )
listedNames =
VAR _tbl = ADDCOLUMNS ( dataTbl, "flag", [filterMeasure] )
RETURN CONCATENATEX ( FILTER ( _tbl, [flag] = 1 ), [Name], ", " )
Best Regards,
Alexander
User | Count |
---|---|
84 | |
80 | |
72 | |
71 | |
55 |
User | Count |
---|---|
108 | |
106 | |
93 | |
84 | |
66 |