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?
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.
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.
@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 :
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.
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 ?
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 🙂
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?
Check out a full recap of the month!
Check out the winners of the recent 'Can You Solve These?' community challenge!
Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.