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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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 ?

Hi @Anonymous,

 

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

Anonymous
Not applicable

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?

amitchandak
Super User
Super User

@Anonymous , 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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

Back2Basics
Helper IV
Helper IV

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. 

kriscoupe
Responsive Resident
Responsive Resident

Hi @Anonymous,

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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