Forum Discussion
Filtering by Date
Sevearal Issues
- You are using a calculate column. These are calculated ONCE when the table is created and at that time there is no slicer applied. You need to use measures and measures need to be agregate or be funcitons that return a unique result. Remember PowerBI applies a filter context to the cell its caluclate on and if you have a single row then MAX = MIN = AVG = the unique value of that row
- Filtering on the table itself can cause issues and yield inconsitant results
- PowerBI DateTime intellgence functions make it very easy but require a date table. So in this case a disconnected slicer it not necessary if you just want to define max date.
See this example using your data. Bottm Left Table is the way PowerBI will display by default with the simple mesure of the difference between the two dates. The Top Right Table is filtered using a Visual filter to only show positive results. The Right Chart is foricing PowerBI to show all results for ID even if mesure is NULL by default will only show rows that have calculations non-null results.
Thanks. That is helpful. Here is a file I am playing with some data on (https://www.dropbox.com/s/loc8mh2fqa7uk6p/Test.pbix?dl=0).
I have a tables of dates that I have generated. I am then trying to use the Max Date from the disconnected slicer in a caculation on the Applicants tables. You can see if I create a measure in the Applicants table referencing the max filter date from the slicer, it shows the right date. However, if I try to do a calculation where I subtract two dates, it doesn't use the max date I chose in the slicer, but instead use the max date in the disconnected date table regardless of what is chosen in the slicer.
What I am doing wrong. I think if I can get this straight then my plan will work on how I want to be able to filter my data.
Thanks in advance for your help.
I wanted to bump this back up to see if anyone had any help they could give.
Sevearal Issues
- You are using a calculate column. These are calculated ONCE when the table is created and at that time there is no slicer applied. You need to use measures and measures need to be agregate or be funcitons that return a unique result. Remember PowerBI applies a filter context to the cell its caluclate on and if you have a single row then MAX = MIN = AVG = the unique value of that row
- Filtering on the table itself can cause issues and yield inconsitant results
- PowerBI DateTime intellgence functions make it very easy but require a date table. So in this case a disconnected slicer it not necessary if you just want to define max date.
See this example using your data. Bottm Left Table is the way PowerBI will display by default with the simple mesure of the difference between the two dates. The Top Right Table is filtered using a Visual filter to only show positive results. The Right Chart is foricing PowerBI to show all results for ID even if mesure is NULL by default will only show rows that have calculations non-null results.
This has been very helpful. Thanks!