Forum Discussion
Helper IIIDates based on Slicer selection
Hi,
I have two tables Fact and Date table, both are link many to one with Fact(data_date) and Date table(Dates).
Fact Table:
| Project | Data_Date | Actual Date |
| Project1 | 5/22/2025 | 11/19/2030 |
| Project1 | 5/29/2025 | 12/18/2030 |
| Project1 | 6/5/2025 | 1/16/2031 |
| Project1 | 6/12/2025 | 2/14/2031 |
| Project2 | 5/22/2025 | 3/1/2031 |
| Project2 | 5/29/2025 | 3/16/2031 |
| Project2 | 6/5/2025 | 3/31/2031 |
| Project2 | 6/12/2025 | 4/15/2031 |
| Project3 | 6/19/2025 | 5/5/2032 |
| Project3 | 6/26/2025 | 5/6/2032 |
Dates Table:
| Dates |
| 5/22/2025 |
| 5/29/2025 |
| 6/5/2025 |
| 6/12/2025 |
| 6/19/2025 |
| 6/26/2025 |
| 7/3/2040 |
| 7/5/2042 |
| 7/6/2042 |
| 7/7/2042 |
| 7/8/2042 |
I have slicer from Fact table(Data_date)-single select, I would like to display Actual Date from Fact table where Fact(Data_Date)<=max(Data_Date) in matrix. Value(Actual Date) will be change based on Dates slicer.
For example if date selected=6/12/2025 then output will be upto data_date 6/12/2025:
| Dates(Date table) | ||||
| Project | 5/22/2025 | 5/29/2025 | 6/5/2025 | 6/12/2025 |
| Project1 | 11/19/2030 | 12/18/2030 | 1/16/2031 | 2/14/2031 |
| Project2 | 3/1/2031 | 3/16/2031 | 3/31/2031 | 4/15/2031 |
if date selected=6/19/2025 then output will be upto data_date 6/19/2025:
| Dates(Date table) | |||||
| Project | 5/22/2025 | 5/29/2025 | 6/5/2025 | 6/12/2025 | 6/19/2025 |
| Project1 | 11/19/2030 | 12/18/2030 | 1/16/2031 | 2/14/2031 | |
| Project2 | 3/1/2031 | 3/16/2031 | 3/31/2031 | 4/15/2031 | |
| Project3 | 5/5/2032 |
, how can i achive using DAX ?
Hi Jyaul1122,
Thank you for reaching out to the Microsoft fabric community forum. Aslo thanks to lbendlin, for his inputs on this thread. I reproduced the scenario again, and it worked on my end. I used my sample data and successfully implemented it.
Relationship:Outcome:
I am also including .pbix file for your better understanding, please have a look into it:
Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.
Thank you for using the Microsoft Community Forum.
5 Replies
- lbendlin
Super User
I have slicer from Fact tableThat is not recommended. Slicers should come from dimension table columns. Otherwise you are forced to use "unnatural" bidirectional relationships and bridge tables.
Refactor your approach to use the data model as it was intended. Use TREATAS to transfer filters if your tables are disconnected.
v-kpoloju-msftCommunity Support
Hi Jyaul1122,
Thank you for reaching out to the Microsoft fabric community forum. Aslo thanks to lbendlin, for his inputs on this thread. I reproduced the scenario again, and it worked on my end. I used my sample data and successfully implemented it.
Relationship:Outcome:
I am also including .pbix file for your better understanding, please have a look into it:
Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.
Thank you for using the Microsoft Community Forum.- v-kpoloju-msft
Hi Jyaul1122,
Just checking in to see if the issue has been resolved on your end. If the earlier suggestions helped, that’s great to hear! And if you’re still facing challenges, feel free to share more details happy to assist further.Thank you!
- v-kpoloju-msft
Hi Jyaul1122,
Hope you had a chance to try out the solution shared earlier. Let us know if anything needs further clarification or if there's an update from your side always here to help.Thank you.
