Stuck on many-many relationship

[jcbutts]

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 same PO numbers, since many values are repeated day over day (though each PO will only show up once per day).  I need to create some visuals that use data from the two tables, but I get some form of "can't display the visual". 

I've created a separate date table.  In order to avoid the many-many relationship between the "capstone" and "zordn(2)", I also created a reference table "zordn(3)" with all duplicates removed for the PO column.

The dates table is connected to the report date of each table.  The "capstone" and "zordn(2)" tables are connected by the "PO" field common on each one.

I can use the date field to create visuals that only reference "capstone" or that only reference "zordn(2)", but not both at the same time.  Is there another way to accomplish this?

[jcbutts]

Update:  I've paired down the zordn(3) table to only have one column of data:  PO number.  I can get the model to show visuals using that PO number and some of the information from both tables simultaneousl (like "rank" from each table).  But as soon as I try to add other columns I get the greyed out visual and an error saying it couldn't detect the relationship.

Not sure why it can find the relationship for some columns but not others?

[jcbutts]

Another update:  I can now get the data to show up by making one of the relationships to zordn(3) bidirectional.  While that will now allow me to see columns like "appt day/time" in my visual, if I take the "rank" columns out of my visual it says it can no longer detect the relationship.

I guess it's not the end of the world, but I'm really confused as to why removing a column from a visual table would cause it to break.

[edhans]

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]

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!