cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Independent Date Filter

Hello, I'm working on a report that contains an appended table with three dates (created date, start date, completed date) within one table. If I add a date slicer for any one of these dates, it will not include all calendar dates for 2020. To remedy this, I created an independent date table, related the date field to all three of those dates in the model, but made the connection inactive. I tried using the relationship DAX formula in a created measure to relate the dates, but I cannot get the indepedent date table in the slicer to work. I want that filter to show results that include dates from any of the three date fields. What am I missing?

 

Thanks in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User VII
Super User VII

Re: Independent Date Filter

@levelup see attached, I hope this is what you are looking for, you can use date slicer to see the numbers.

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

10 REPLIES 10
Highlighted
Super User VII
Super User VII

Re: Independent Date Filter

@levelup if you have used these measures in your visual, and assuming you have used userelationship function in your measures to make an inactive relationship to active, all measure values will be shown based on the selected date range, and it should work. Please provide more context with example/screenshots if this doesn't answer.

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Helper I
Helper I

Re: Independent Date Filter

Hi@parry2k ,

 

I did try using the userelationship  in a measure, but it didn't work. This variable is used in a visual to count tasks. The independent date slider is formatted as a date between filter, but when I slide it, it doesn't produce any results.

 

This is the formula I used. I tried relating it to one date, which didn't work, and then tried relating it to all dates, which also didn't work.

 

task ID screenshot.PNGtask ID screenshot 3.PNGtask ID screenshot 2.PNG

 

Thank you!

Highlighted
Super User VII
Super User VII

Re: Independent Date Filter

@levelup you will create 3 different measures, for create, due, and completion date, and in each measure you will use userelationship function and these use these 3 measures to visualize the data. I hope it makes sense.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Helper I
Helper I

Re: Independent Date Filter

@parry2k  Three different measures for the same variable? I'm not quite following. 

Highlighted
Super User VII
Super User VII

Re: Independent Date Filter

@levelup yes 3 different measure, one for each stage.  Maybe it will be easier if you paste sample data and expected output. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Helper I
Helper I

Re: Independent Date Filter

@parry2k  Thanks for the guidance. 

 

The expected output is for any tasks that are created, started, or completed within a specific time period should appear in the visualizations based on the parameters in the independent date filter. 

 

Sample Data 

Highlighted
Super User VII
Super User VII

Re: Independent Date Filter

@levelup it is not letting me download the file, no access to the file. 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Super User IX
Super User IX

Re: Independent Date Filter

@levelup , You can activate the joins using userelation as long you need the same dates to filter.

Means one date dimension all three dates can work

Refer the example: https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

 

Sometime you may need two date dimensions - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601 (This is for one date. But you can do for two dates )



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper I
Helper I

Re: Independent Date Filter

Sorry, @parry2k  try this one: Sample Data 

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors