Forum Discussion
Helper IIIFilter data in the table visual based on date selected in the slicer
Hi Folks,
I have a date slicer which is based on simple calendar table.
I want to get the selected date in the date slicer and filter the data in the table visual if the date selected in the slicer falls in the date range of the date column in the table visual.
For this I tried to create a DAX Measures: Max Date, Min Date to get the maximum and minimum values in the date slicer.
Then for calculating the date difference, I was using a formula like this in a calculated column
The Slicer can not affect the table view, the calculated column can not be filtered by slicer.
You can use the measure to get the correct value:
_MaxDateDiffColumn = DATEDIFF([Max Date],MAX(FollowUpCallSMSData[Call Attempt Date 1]),DAY)_MinDateDiffColumn = DATEDIFF([Min Date],MAX(FollowUpCallSMSData[Call Attempt Date 1]),DAY)Then you shouldn't drag the [Max Date] and [Min Date] to the table visual, because the 'Date' table has no relationship with 'contact' and 'FollowUpCallSMSData', when put the two measures in the visual that will result in crossfilter.
So the result is as follow:
Best Regards
Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
24 Replies
- elitesmitpatel
Solution Supplier
The Problem is the Relationship
Here Date realtionship is missing with all the table. Make the realtionship of date with the table you used in table.
pavanmanideepI have to compare if Call Attempt Date 1, Call Attempt Date 2, Call Attempt Date 3,followupcallend from the FollowupCallSMSData table are all in range with the data selected in the slicer, so I thought not creating a relationship and calculate the differences using DAX functions and set this as a filter for the Table visual.
But I am struck and cant proceed further on this. Please suggest if there is any alternate better way to achieve this.
Thank you.
- foodd
Community Champion
Hello pavanmanideep , and thank you for sharing your PBIX, would you similarly share the source data as used by the PBIX so that elitesmitpatel and Ritaf1983 or other members of the Community are able to refresh the model while investigating your issue.
If your requirement is solved, please make THIS ANSWER a SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.
- Ritaf1983
Super User
Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.
How to Get Your Question Answered Quickly
- pavanmanideep
Thank you Ritaf1983 for your reply, please find the PBIX attached for your reference in one drive
https://1drv.ms/u/s!Ahfda78dKUx-khTg8-AjEfblerhx?e=D2M1SF
You can find the data here
- Ashish_Mathur
Hi,
I have solved a similar question in the attached PBI file.
Hope this helps.
- pavanmanideep
Ashish_Mathur I was retrieving the date selected in the slicer and showing the Max Date, Min Date with a measure in the FollowupCallLogSMSData table. If I am able to compare the dates from slicer with the dates (FollowupCallDate 1,2,3, Actual End), I don't need to show Max Date, Min Date in the visual and if any of fact table dates fall in the date range of slicer, I have show in the table visual, so here is the expected visual I am looking for
Hope I was able to convey...thank you very much for your time.
- pavanmanideep
Thanks Ashish_Mathur , so here how I can expect my visual to be.
I was looking to get the date selected in the slicer and filter the data in the table visual when any of the date fields
CallAttemptDate1, CallAttemptDate2, CallAttemptDate3, ActualEnd falls in the date range of the date slicer.
Thank you for your time.
- elitesmitpatel
I have tried to solve. Please have a look
Filter Data in table visualNote - Look at the date and the column from which it is selected. If it is wrong then please mention the date column of the tables according to which you want to see data.
Thanks- pavanmanideep
Thanks elitesmitpatel , I just tried to filter the data, as without that the visual was unable to render as it has more amount of data. Minimum I have two dates in fact table: Call Attempt Date and Actual End date for which I have to check if they were in range. I think a measure is needed to check this condition if a record with any of these dates are in range. Can you advise me on that Dax for that filter? Any reference would also be helpful.
- pavanmanideep
Hi elitesmitpatel Ashish_Mathur foodd Ritaf1983 , any more suggestions on this, thank you for all your time, do let me know if there was any other approach to solve this.
The Slicer can not affect the table view, the calculated column can not be filtered by slicer.
You can use the measure to get the correct value:
_MaxDateDiffColumn = DATEDIFF([Max Date],MAX(FollowUpCallSMSData[Call Attempt Date 1]),DAY)_MinDateDiffColumn = DATEDIFF([Min Date],MAX(FollowUpCallSMSData[Call Attempt Date 1]),DAY)Then you shouldn't drag the [Max Date] and [Min Date] to the table visual, because the 'Date' table has no relationship with 'contact' and 'FollowUpCallSMSData', when put the two measures in the visual that will result in crossfilter.
So the result is as follow:
Best Regards
Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
