Forum Discussion

jcbutts's avatar
jcbutts
Icon for Helper I rankHelper I
5 years ago

Stuck on many-many relationship

Hi,   I normally try to find an example that matches my issue, but I need to get specific on this one.    I have two primary tables, "capstone" and "zordn(2)".  Both of them have multiples of the...
Need Help
edhans's avatar
edhans
Icon for Community Champion rankCommunity Champion
5 years ago

As long as you are using the date from the date table and not from capstone or zordon 2 (the one on the side), I see no reason why the visuals will not use data from both. You cannot use it to reference zordon 3 at the top without using some bi-directional logic in the DAX via the CROSSFILTER() function.

I'd need to see the PBIX or some good sample data though to go much further.
How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

jcbutts's avatar
jcbutts
Icon for Helper I rankHelper I
5 years ago

I'm definitely only using the date from the Dates table.  Is there a better way to do this than using that Zordn(3) table?  What if there are PO's in one table that don't show in Zordn(3)?  Could that what's preventing this from working?

 

If I create a simple table that uses the PO field from each table, that alone causes it to grey out (using the Dates field as a slicer).  In theory, the data from those two columns should match.  However, PBI can't detect the relationship.

 

I can't share files as the data aren't public, but here are dummy values to give you an idea of how the tables are set up:

 

CAPSTONE

 

Count.Report Date     PO Number       Appt Day/Time                 Rank     Flag

7/1/21                        EW1445              7/18    8am                      1          new

7/2/21                        EW1445              7/18    8am                      2

7/3/21                        EW1445              7/18    8am                      3     

7/4/21                        EW1445              7/18    1pm                      4         change

7/1/21                        MM821              7/29    5pm                      1          new

7/2/21                        MM821              7/29    6pm                      2          change

7/3/21                        MM821              7/30     8am                     3          change

7/4/21                        MM821              7/30     8am                     4

 

ZORDN(2)

Report Date          PO            Order Type           Rank         Flag           State      Deliv Date

7/1/21                  MM821        ORD                    1              new            AZ         7/18/21

7/2/21                  MM821        ORD                    2                                 AZ         7/18/21

7/3/21                  MM821        ORD                    3              change       AZ         7/19/21

 

That's the basic idea.  "Flag" and "Rank" are calculated columns.  They don't necessarily need to match across both tables.  I've only connected the "Dates" table to the "report date" column of each table.  Let me know if you have any other ideas.

 

Thanks!