Forum Discussion
Filter multiple date fields with 1 slicer
I have 4 visuals showing projects in different statuses. I want to show the differet project types in a specified date range by using one slicer the user can adjust. One of the problems I am having is creating a DAX measure that makes it so one slicer can control 3 different date fields, I only want the user to have to put in their date range once.
The 4 visuals-
New Projects: Any project where the 'Added Date' field is between the user specified date range.
Active: Any project where the 'Status Date' field is between the user specified date range and the Finished Date field is null.
Pending Projects: Any project where the 'Status Date' field is NOT between the user specified date range and the Finished Date field is null.
Finished Projects: Any project where the 'Finished Date' field is between the user specified date range.
I would appreciate any help on how to accomplish this!
Thank you!
Hi S_Berg ,
Sorry, as far as I know, it may not be possible to use only one measure to filter 4 Visual different date fields, which will cause conflicts in the conditions, you can create 4 measures and place them in different Visual settings is=1.
Here are the steps you can follow：
1. Create measure.
New Projects = var _mindate=MINX(ALLSELECTED('Date'),'Date'[Date]) var _maxdate=MAXX(ALLSELECTED('Date'),'Date'[Date]) return IF( MAX('Table'[Added])>=_mindate&&MAX('Table'[Added])<=_maxdate,1,0)ACTIVE = var _mindate=MINX(ALLSELECTED('Date'),'Date'[Date]) var _maxdate=MAXX(ALLSELECTED('Date'),'Date'[Date]) return IF( MAX('Table'[Status])>=_mindate&&MAX('Table'[Added])<=_maxdate&&MAX('Table'[Finished])=BLANK(),1,0)Pending Projects = var _mindate=MINX(ALLSELECTED('Date'),'Date'[Date]) var _maxdate=MAXX(ALLSELECTED('Date'),'Date'[Date]) return IF( MAX('Table'[Status])<_mindate|| MAX('Table'[Added])>_maxdate&&MAX('Table'[Finished])=BLANK(),1,0)New Projects = var _mindate=MINX(ALLSELECTED('Date'),'Date'[Date]) var _maxdate=MAXX(ALLSELECTED('Date'),'Date'[Date]) return IF( MAX('Table'[Added])>=_mindate&&MAX('Table'[Added])<=_maxdate,1,0)2. Place the four Measures in different Visual Filters and set is=1.
3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
3 Replies
S_Berg You need a Complex Selector. Re: The Complex Selector - Microsoft Fabric Community
Sample data and expected output would help to be more specific.
Hi Greg_Deckler,
Thank you for your response! I want to Create a tracker like this:
Here is some sample data:
Project Added Deadline Lead Category Status Finished A 1/10/2023 Person 1 Infrastructure 5/12/2023 B 2/24/2023 Person 2 Response 7/5/2023 7/5/2023 C 2/1/2023 3/31/2023 Person 3 Customer 6/23/2023 D 3/31/2023 11/17/2023 Person 4 Admin 5/12/2023
Hi S_Berg ,
Sorry, as far as I know, it may not be possible to use only one measure to filter 4 Visual different date fields, which will cause conflicts in the conditions, you can create 4 measures and place them in different Visual settings is=1.
Here are the steps you can follow：
1. Create measure.
New Projects = var _mindate=MINX(ALLSELECTED('Date'),'Date'[Date]) var _maxdate=MAXX(ALLSELECTED('Date'),'Date'[Date]) return IF( MAX('Table'[Added])>=_mindate&&MAX('Table'[Added])<=_maxdate,1,0)ACTIVE = var _mindate=MINX(ALLSELECTED('Date'),'Date'[Date]) var _maxdate=MAXX(ALLSELECTED('Date'),'Date'[Date]) return IF( MAX('Table'[Status])>=_mindate&&MAX('Table'[Added])<=_maxdate&&MAX('Table'[Finished])=BLANK(),1,0)Pending Projects = var _mindate=MINX(ALLSELECTED('Date'),'Date'[Date]) var _maxdate=MAXX(ALLSELECTED('Date'),'Date'[Date]) return IF( MAX('Table'[Status])<_mindate|| MAX('Table'[Added])>_maxdate&&MAX('Table'[Finished])=BLANK(),1,0)New Projects = var _mindate=MINX(ALLSELECTED('Date'),'Date'[Date]) var _maxdate=MAXX(ALLSELECTED('Date'),'Date'[Date]) return IF( MAX('Table'[Added])>=_mindate&&MAX('Table'[Added])<=_maxdate,1,0)2. Place the four Measures in different Visual Filters and set is=1.
3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
