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

Date Slicer not working

Hi,

I have two tables and each table has two common columns (Date and Customer) that I want to slice and dice. The Date slicer does not filter TableB, how to solve this? I came across articles about USERELATIONSHIP, does this work in my scenario?

 

image1.jpgimage2.jpgimage3.jpg

6 REPLIES 6
Highlighted
Resolver IV
Resolver IV

Re: Date Slicer not working

Hi @marc_hll,

 

You need to model your data differently. You should create 2 dimension tables, a customer dimension and a date dimension. The customer dimension should include all the unique customers from both tables and the date dimension should include all the dates in range from the 2 tables. When you relate these new dimension table to your 2 tables the relationships should have 1 - * instead of * - * and you will not have inactive relationships (ones with dotted lines) anymore. Then you can slice and dice by both customer and date across the 2 tables with ease.

 

Hope it helps.

 

Kris

Highlighted
Helper III
Helper III

Re: Date Slicer not working

The date that you are trying to filter on is only in one of the tables. Although it looks like the dates are in both it is different data.

 

I think you can work around this though. If you duplicate one of your tables, I would suggest the one with the most rows, then remove all columns except for the date column. Rename the table to something sensible like 'Date'. Then recreate a relationship between this new table and each of your other tables, both directions. Now you should be able to create your filter using this new table and the date field from it. This should then filter across both for you. 

 

I've not tried this out, but thinking it should work. 

Highlighted
Super User IX
Super User IX

Re: Date Slicer not working

@marc_hll , Create a common customer and Date table and use those.

 

Customer = distinct(union(all(TableA[Customer]),all(TableB[Customer])))

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.



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 II
Helper II

Re: Date Slicer not working

After I created a unique/distinct table of customer and also create a date dimension, I could not link them because it gives me an error circular dependency. If I would like to use userelationship method, what will be the example? I dont have amount here. Another question is the date table that I created as dimension from 1/1/2020 - 12/31/2020 still display the whole year after connect to fact TableA and TableB, how to show only 7/1/2020 - 7/3/2020 ?

Highlighted
Resolver IV
Resolver IV

Re: Date Slicer not working

Hi @marc_hll,

 

If you have circular dependency after making the customer/date dimensions it must be because you haven't removed the original relationships between the fact tables. You should not need these anymore. Also make sure that your relationships that are now one to many (1 - *) are set to single in terms of their direction. In the visuals and slicers you should now use the columns for customer and date from the newly created dimension tables to slice and dice.

 

All should be good then 🙂

 

Kris

Highlighted
Helper II
Helper II

Re: Date Slicer not working

Hi @kriscoupe,

 

I didnt see any existing original relationships between the fact tables. Thanks for your reply

I'm curious to know if USERELATIONSHIP can help my inactive relationship scenario, anyone?

Helpful resources

Announcements
Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Upcoming Events

Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

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.

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Top Solution Authors