I have tried to come up with a solution, but have failed unfortunately.
I need a single slicer on my main page of my report, that will filter all pages based on this date.
The problem I have is that I have 2 dates in my data. Opened date and closed date (this is for tickets logged)
If I apply created date as the date for my slicer, then it will show all tickets opened in that month, but will only show tickets closed in that month which were also opened in that month.
So I may have tickets closed in July which were opened in march, april, may etc... but with the Slicer I have in place currently, it will only show me tickets that were closed, which were also opened in the same month.
I hope this makes some sense
Solved! Go to Solution.
Hi @jondurbridge ,
Just look at a simple model:
ID Start Date End Date
I have created a calendar Table and make two relationships with the previous table:
1 to Many - Start Date (Active)
1 to Many - End Date (Inactive)
I also created 2 measures:
Start Count = COUNT(Table1[ID]) + 0 End Count = CALCULATE(COUNT(Table1[ID]); USERELATIONSHIP('Calendar'[Date];Table1[End Date]))+0
The first one since there is an active relationship between start date and calendar is direct, the second I need to force the use of the inactive relationship. The + 0 on the end of both measure is to get values instead of blanks in order to have all months shown on the chart otherwise the months wihout data wouldn't be showned.
Using the Calendar table as my slicer and my axis on a simple chart you can see the calculation are presented according to slicer selection:
You can then sinc the slicer on several pages.
Check the PBIX file attach.
Appreciate the help, but I just cant see how I would apply that to my problem.
I have a ticket, which has a created time and a completed time.
Any graph I use has to have created or completed time as the axis.
This means that if i use created time as the axis, it will only show me tickets created during that time, which have now been completed.
So if we take October, it will not show me all tickets completed in October, only those that were also created in October
Hi @jondurbridge ,
That's the point of making the bridge table, you will not use the Start and Close date on your visuals you will use the Calendar table then with the measures you will fullfill your data.
So when you see a October chart you have the correct calculation for Start and Close dates.
If you want to have tickets open at a certain date then you need to do a different calculation, again this calculation will be based on the relationship between your tables.
The problem with your solution MFelix........you are clearly far brighter than me, and I'm struggling to get it to work
I have follwed the advice, but when I create a new date table, set the relationships correctly, they do not appear to work.
So forget about the fact that I have create and complete.
I have created a new date table by using Date = CALENDARAUTO(6)
Once the relationship is made, the existing create date field is no longer seen as a date in powerbi.
I set the slicer to the new "Date" as created above, but it does not filter by month and year etc.. If I select all, it shows me all the data as one big sum, but even using drilldown, it does not go to a month by month level.
Hi @jondurbridge ,
When you create the relationship, the "hierarchy" is showned in the calendar table.
As you can see below then you need to use the calendar table for your axis.
This is exactly what I have done. No matter what I do, the new date table does not have any bearing on the data from the other table. The relationship is active, and at present im only trying to filter on one date (create time)
I have a table where there are thousands of dates (DATE) which has a relationship with (Create_Time)
When I perform any filter on the new (DATE) it does not find any data relating to those dates at all.
Am i missing something blindingly obvious?
Do the dates in my new date table have to mirror the dates in the original table? Or can PowerBI realise that 01/01/2019 11:31 should show in a filter for 01/01/2019 ...
Thanks again for the help