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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
o59393
Post Prodigy
Post Prodigy

Date relationship problem

hi all

 

I have 2 tables, both contain a day by dat date column in yyyy-mm-dd format.

 

There is a code asociated with each of the days. Code SP/B-0144.11 starts on 2/15/2019, while SP/B-0171.00 goes from 1/1/2017 to 2/14/2019.

 

When I create a table I see the following:

 

 

The year filter has effect on the left table however is duplicating the code per day. When it should only show once per day.

 

On the right table the code is showing correctly per date, however the year filter is not having effect.

 

My relationship looks like this

 

 

 

The bridge is done by merge columns, both consist of the combonation of country+product name.

 

I tried doing a 3rd table which is a calendar and I tried to link it to both existig tables but i got one inactive relationship

 

Here is the pbix

 

The expected solution is to get both tables work with the year filter, and have one day only per row (no duplicates) showing the respective code, where the transition of code would be 2/15/2019.

 

Any idea how to get it ok?

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

Get rid of the many-to-many, they are evil, make things not work properly and are almost always avoidable. Implement a bridge table of unique key values.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

8 REPLIES 8
Greg_Deckler
Super User
Super User

Get rid of the many-to-many, they are evil, make things not work properly and are almost always avoidable. Implement a bridge table of unique key values.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler 

 

The problem is that products are added on a daily basis. Maintain a key of product+country will be kinda difficult.

 

Any suggestion?

Bridge Table = 

  DISTINCT(

    UNION(

      SELECTCOLUMNS('Query1',"Merged",[Merged]),

      SELECTCOLUMNS('Formulas',"Merged",[Merged])

    )

  )

 

Zero maintenance required.

      



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

hi @Greg_Deckler 

 

Here it is:

 

bridge.png

 

But the values are still not filtering correctly:

 

bri1.PNG

 

thanks!

Try making the relationships Both direction



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

it's not showing left table and right table will not show only 2019.

 

Thanks again!

OK, let's back up, what are we trying to do here overall?  Take a look at what I did with your date table (Table) and the Model. Use your dimension tables like your calendar table and bridge table to affect both of your fact tables.

 

Attached below.

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

hi @Greg_Deckler 

 

I realized that my relationship model is not so good, and you confirmed me that by avoiding  many to many relationships. In fact im havind some issues creating dax, so I need to get this relations set properly. 

 

I checked your solution and found out that for example when i select a brand, the code wont filter at all:

 

 

cod111.PNG

 

bbn2.PNG

 

For example for CR I have the following codes for that brand: 

 

bbn.PNG

 

I did a table with the code + column date of formula and another table  with code + date of query1 and didnt work well:

 

Capture.PNG

 

https://1drv.ms/u/s!ApgeWwGTKtFdhks4LrGKQB-RfqCQ?e=tQFLfi

 

Appreciate your help!

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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