Forum Discussion
Filtering by Date
I am trying to figure out how to filter a report based on data and a status of an applicant. I was able to do this in Tableau b/c Tableau supports a user entering a parameter and then having dynamically calculated fields based on the parameter value selected by the user. However, it doesn't look like I can approach it this way with PBI. So I am looking for input on how to accomplish my goal in PBI.
Here is a sample of some of my data:
Applicant ID Pending Date Accepted Date
001 10/12/2017 12/1/2017
002 1/1/2018 3/15/2018
003 5/2/2018
004 6/1/2018 7/15/2018
If someone chooses a date of 1/15/2018 the visualization would show 1 applicant pending and 1 applicant accepted. If a user was to select 7/20/18 it would show 1 applicant pending and 3 applicants accepted, etc.
Any suggestions?
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.
8 Replies
- The approach will work just fine in PowerBI but either the filter context of the relationship isn’t defined properly or you may need to customize the DAX. Can you share a PBIX file or some additional details of your data, relationships, visualizations and measures your using.
Here is a more complex scenario that demonstrates using disconnected slicers to define variables and have dynamic calcutions based on them to overlay two different times lives on top of each other based on relative week from the start of a selected date range. https://1drv.ms/u/s!AuCIkLeqFmlhhJkIcpsdkWzMMqeBBwThanks. 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.
Hi rrhutch,
In Power BI, we also could use parameter in Query Editor to filter the data.
You could have a reference of the blog below.
https://www.mssqltips.com/sqlservertip/4475/using-parameters-in-power-bi/
Best Regards,
Cherry
Thanks. I was aware of parameters availble with this method, but I am waiting for Microsoft to take it to the next step so that an end user can interact with parameters in the PBI Service and not just deskop. So I am hoping that sometime soon Microsoft completes this effort.
rrhutch there are some hacks that allow this https://guyinacube.com/2017/05/dynamic-power-bi-reports-using-parameters/
