Forum Discussion

o59393's avatar
o59393
Post Prodigy
6 years ago
Solved

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?

  • 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.

8 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion

    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.

    • o59393's avatar
      o59393
      Post Prodigy

      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?

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion

        Bridge Table = 

          DISTINCT(

            UNION(

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

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

            )

          )

         

        Zero maintenance required.