Forum Discussion
Using the dates from date slicer in queries to filter data for visualization
Hi All,
I am a newbie to Power BI, I need some help trying to build a map dashboard with date slicer. I need to be able to utilise the start and end dates selected in the date slicer and use them as parameters to filter the data from my queries or somehow apply those filters in Power BI.
eg. I have a HR database with Hire Date and Termination Date and I am creating a visualization based on the number of employees active in the period selected by user and analyze their leaves, pay , overtime in the period selected from a database.
I need to be able to fetch the records where Hire Date <= End date selected in the date slicer and Termination date is either NULL or >= start date selected in date slicer. I am utilizing a date dimension table for the slicer and have created measures start date as MIN date and end date as MAX date from slicer.
Any ideas?
10 Replies
Clarifying on the question -
Correlating it with paramterised SSRS dashboards, where user has the ability to change the dates as required and the underlying queries fetch data based on those parameters, Is it possible to have similar ability for user in Power BI?
Select date range from date slicer and use the start and end date in underlying queries as there are multiple date fields in queries.
Any advise will be appreciated.
v-caliao-msftMicrosoft Employee
Slicer no works like Parameter that works in SSRS. Slicer only effect on Visual level. We cannot use slicer items in dataset or query. What you can do is use Power BI parameter in query. Please refer to the link below to see the details.
https://powerbi.microsoft.com/en-us/blog/deep-dive-into-query-parameters-and-power-bi-templates/Regards,
Charlie Liao
Thank you for your response.
I explored the parameters option , however I haven't been able to figure how how parameters can be changed by the user and the report/dashboard to show the related data.
Essentially. I am trying to use start date and end date (enterable by user) to filter rows in query based on conditions eg. Start date = '2017-05-01' and End Date '2017-05-31' then I need my queries to use these dates as filters - select [Empname],[EmpHireDate],[EmpTermDate] from Employee where EmpHireDate <='2017-05-31' and ( EmpTermdate IS NULL or EmpTermdate > '2017-05-31')
this will give me the active employees in that date range.
I did find a workaround though, it was not quite hard to implement. Any ideas will be appreciated.
electrobritPost Patron
Were you able to solve this? I am having same issue.
I need to know based on the date selected by the user the active stores. They have termination dates and anticipated active dates so being able to see how many are active as of a date is important.
Hi,
I can tell you what my findings were, I had a hard time figuring this out as Power BI apparently doesnt work like SSRS or most other reporting tools. the only way I could work around this was by flattening my dataset. I joined my dataset with a calendar dimension and generated one row of an entity for each monthyear that entity was to be filtered for and a column with monthyear. Then I set the dateslicer in Power BI with that monthyear column which allowed slicer to filter records for each monthyear.
If the relationships are set right with all other datasets, then the slicer will filter data for everything. Mostly I found it was the relationships not set correctly which caused data to not filter correctly once the base data was all set correctly.
Hope this helps.
Hi,
Did you find a solution. I'm trying to show the active current asat date. In SQL and Tableau I'm able to achieve using the below where [Date] is my Parameter
[Start Date] <= [Date] AND IFNULL([End Date],[Date]) >= [Date]
Thanks
Jag
