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.
Dear Community,
I am beginner in DAX and kindly ask you for your help with following issue:
I have two tables whereas one date table should act as a dynamic filter for the other table.
The date table looks like this:
Date | Event |
01.01.2019 | |
02.01.2019 | Board Meeting Q1 |
03.01.2019 | |
04.01.2019 | |
05.01.2019 | |
06.01.2019 | |
07.01.2019 | |
08.01.2019 | |
09.01.2019 | |
10.01.2019 | |
11.01.2019 | |
12.01.2019 | Board Meeting Q2 |
13.01.2019 | |
14.01.2019 | |
15.01.2019 |
With applied filter the other table should show only entries "after latest demand board until today". Any idea how I could set up the filter/measure?
Thanks a lot!
Greetings
Juni15
Solved! Go to Solution.
Hi @Anonymous ,
Use the following code for you filter mode:
Filter = IF ( MAX ( Events[Resolution Date] ) >= CALCULATE ( MAX ( 'Calendar'[Date] ); FILTER ( ALL ( 'Calendar'[Event]; 'Calendar'[Date] ); 'Calendar'[Event] <> BLANK () && 'Calendar'[Date] <= TODAY () ) ); 1; 0 )
Don't know if your tables are connected or not but works in the same way if you use the resolution date on your visual.
Check PBIX file attach. On the file I have place two table the one that as the filter column is just for you to check that the 1 and 0 are on the correct data.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Anonymous ,
Although your information is reduced, there is several options to have the result you want:
Filter = IF(MAX(Events[Date]) >= MAX('Calendar'[Date]) ; 1 ; 0)
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks a lot @MFelix
With your proposals I would get a list with an end date as filter (or i got it wrong :D). I am more looking for a dynamically changing date range from "last meeting until today" to filter the other table "resolution date".
The date table with Events looks like this:
Date | Event |
15.07.2019 | |
16.07.2019 | Board Meeting Q1 |
17.07.2019 | |
18.07.2019 | |
19.07.2019 | |
20.07.2019 | |
21.07.2019 | |
22.07.2019 | |
23.07.2019 | |
24.07.2019 | |
25.07.2019 | |
07.11.2019 | Board Meeting Q2 |
08.11.2019 | |
09.11.2019 | |
10.11.2019 |
It should filter this table (results are red marked)
Resolution Date | Demand |
15.07.2019 | ABC123 |
16.07.2019 | ABC124 |
14.07.2019 | ABC125 |
13.01.2019 | ABC126 |
16.07.2019 | ABC127 |
17.07.2019 | ABC128 |
18.08.2019 | ABC129 |
09.01.2019 | ABC130 |
10.01.2019 | ABC131 |
11.01.2019 | ABC132 |
12.01.2019 | ABC133 |
13.01.2019 | ABC134 |
14.01.2019 | ABC135 |
15.01.2019 | ABC136 |
Greetings,
Juni15
Hi @Anonymous ,
Use the following code for you filter mode:
Filter = IF ( MAX ( Events[Resolution Date] ) >= CALCULATE ( MAX ( 'Calendar'[Date] ); FILTER ( ALL ( 'Calendar'[Event]; 'Calendar'[Date] ); 'Calendar'[Event] <> BLANK () && 'Calendar'[Date] <= TODAY () ) ); 1; 0 )
Don't know if your tables are connected or not but works in the same way if you use the resolution date on your visual.
Check PBIX file attach. On the file I have place two table the one that as the filter column is just for you to check that the 1 and 0 are on the correct data.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering 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 |
---|---|
113 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |