Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
jhorta
Frequent Visitor

Date Slicer for multiple columns in different tables

I'm new in the BI and Power BI world, I'm creating a PoC in PowerBI. My data sources are all scattered, mainly on spreadsheets, and I'm also pulling data from Google Analytics.

 

In one of my tables and in the Google Analytics data I have a date field, that is completely unrelated. However, I would like to include a date slicer for the page, so that the user can see the numbers on those dates. The numbers visualized are not related, one is coming from an app DB and the others are related to Google Analytics.

 

So, in order to filter all the page based on one date slicer, I would have to connect them in some fashion which I still don't know what would be the best way to achieve it. Or will I have to code my own slicer?

 

Thanks!

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

Hi @jhorta,

 

You need to create a Calendar table and them make a relationship between the tables that way you canuse the calendar table to make the visuals / slicers you need and all the information will be transform based on that.

 

On this post you can find several ways to create a calendar but if you google "POWER BI Calendar" you will find lots of different ways to create a calendar table.

 

Just as a best pratice be sure that the calendar table is starting on 1 January and ending on 31 December (this can be different years)

 

Regards,

MFelix


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

9 REPLIES 9
MFelix
Super User
Super User

Hi @jhorta,

 

You need to create a Calendar table and them make a relationship between the tables that way you canuse the calendar table to make the visuals / slicers you need and all the information will be transform based on that.

 

On this post you can find several ways to create a calendar but if you google "POWER BI Calendar" you will find lots of different ways to create a calendar table.

 

Just as a best pratice be sure that the calendar table is starting on 1 January and ending on 31 December (this can be different years)

 

Regards,

MFelix


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi @jhorta, I am having a similar issue and have created a Calendar table, but cannot seem to get the relationships to work. I two data tables from different source systems and I'm trying to create a single date filter (month/year) on my visualization page. The data tables have similar data, like order numbers, created date/time, dispatched date/time, completed date/time, etc. I've checked the data types of all the columns and they seem to be the same. Any ideas on what I'm doing wrong? Any feedback would be greatly appreciated.

 

2018-03-13_17-05-12.png

Hi @rbwidener,

 

You are trying to make more than 1 connection between date table and the other tables correct?

 

You need to make inactive relationships between the several dates and then create calculated measures using the USERRELATIONSHIP formula to active the relationship upon context.

 

Regards,

MFelix


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Thanks @MFelix - I've seen some posts about this and will give it a try!

@rbwidener

 

If you need any help or explanation please say.

 

Regards,

MFelix


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Ok, I've created relationships between all of the dates on my two data tables and the calendar data table, then did a USERELATIONSHIP function for the created date on each of the data tables. The tutorial I was reading said that it didn't matter what the calculate function is (https://msdn.microsoft.com/en-us/library/hh230952.aspx) - maybe I listed the columns in reverse... I listed USERRELATIONSHIP('Data Table'[Created Date],'Calendar'[Date]). When I look at my visualization filter, using 'Calendar'[Date], it's blank:

2018-03-14_10-05-13.png2018-03-14_10-03-34.png

 

Any ideas? Thanks for all of your help!

That's interesting. As I mentioned, I'm still learning a lot of things around PowerBI. For me, I read a few tutorials on how to create a Calendar table, at the end I used this one: https://www.youtube.com/watch?v=zXZAZrUwUe8 and customize it to take parameters and some other small things, and I had to reformat one of my date columns which was in an odd format, but PowerBI recognized it as a date when I applied the new format.

 

After creating it, I've just created the relationships using the visual UI, and then dragged and dropped the Slicer using that table, and that's it, everything worked perfectly, nothing else to be done.

 

So, two simple ideas, could it be that you have that kind of date format problem and is not recognizing as a date? Or maybe the Calendar table that you created has an error(maybe the main function is not being executed?)

Thanks for the video & suggestions @jhorta. I'm still not sure why my date relationships weren't working the way I intended, but there were a lot of variables that I could have done wrong... In the end, I created a new "date" table with the names of the months, which was how I wanted to filter my report page. Since each table had a "Completed Month" column, I was able to create a star schema with all active relationships and filter successfully. I'd still like to understand the USERELATIONSHIP function, but that will be another day. Thanks also to @MFelix for your assistance!

 

2018-03-14_13-55-27.png

jhorta
Frequent Visitor

Alright, sounds like I have some more research to do. Thanks!!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.