Relationship error that again doesn't happen in Excel

[masplin]

Thisis the 2nd relationship I have created in Desktop and liek the first one throws a nonsense error. Both work fine in Excel so why is desktop different?  The first one i solved by turning other relationships off then turning all of thme on in a differnet order. This one makes no sense.

I added a new table with 6 columns. Thisis a list sent by a 3rd party of vehicles they think they have booked for my garage.  To check I need ot seach my invoice table to see if the VRM appears whithin a date range.  If I locate a record i then want to know if the customer is new or repeat whih is a column on the invoice table. 

So the caluated column on the new table WCFMC is where "Posted Document Header" is my invoice table

Visit Type = CALCULATE(
VALUES('Posted Document Header'[Visit Type]),
FILTER(
'Posted Document Header',
'Posted Document Header'[VRM]=WCFMC[VRM] &&
WCFMC[Order Date]+45>='Posted Document Header'[Order Date] &&
'Posted Document Header'[Order Date]>=WCFMC[Order Date]-30 &&
RANKX(
FILTER(
'Posted Document Header',
'Posted Document Header'[VRM]=WCFMC[VRM] &&
WCFMC[Order Date]+45>='Posted Document Header'[Order Date] &&
'Posted Document Header'[Order Date]>=WCFMC[Order Date]-30
),
'Posted Document Header'[Posting Date]+'Posted Document Header'[Starting Time],,ASC
)=1
)
)

That all works fine.  however in order to do reports of new/repeat cusotmers across several table I created previosuly a small table "Visit Type" that just contains new and repeat. i have new and repeat number n my invoice table, my table of 2016 finacials and my 2017 budget.  All of these 3 tables have a column Visit Type and these are related to my table Visit type.  This all works fine.

I wanted to also associate my new WFCMY table iwth my Visit Type table for consistency.  As i am new ot Desktop i first built everythnig in excel. This excel was theo ne imported to create desktop so contains al lthe same tables and relationships.  On doing this I get the error below

 

The gibberish in them iddle makes no sesne. I treid it earlier when I had a few more clacuated column and it woudl give an error including one of these columns.

For me thisis a major show stopper for Desktop unless someone can explain why this is creating an issue, but not in excel. I thoguht a circular dependency ws created when you say had 3 tablkes and related A to B, B to C and then C to B, possilby by a longer path.

The onyl thing i can thnik of is the Visit Table has a relationship to the Posted Document Header.  If I turn this off i can thne create my new relationship and then turn the posted document one back on!!!!! Why is this? is this becuase i am filtering that same table in my forumla?  If so seems liek a massive bug as doesn't happen in exce and can be solved by changing the order.

Really appreciate any advice

Mike

[v-qiuyu-msft]

Hi @masplin,

From your description, I'm not clearly about your relationships for all tables. You can share the .pbix file if possible.

To create relationship in Power BI, you can take a look at this article firstly: Create and manage relationships in Power BI Desktop.

By the way, please try to run the latest desktop version.

Best Regards,
Qiuyun Yu

[masplin]

Hi

Managed to cut the file down to disguise the business sensitive information. I have sent you a dropbox link by PM.

So the issue can be recreated as follows:

The calculated columns in table WCFMC are calling on tables Posted document Header and Service Order Header both of which have exisitng relationships to Visit Type, Marketing Type and Order Table.  In the version I have sent i have truned off these 6 relationship.  If you now create a relationship between WCFMC[Visit type] and Visit Type[Visit Type] it works fine.

Now turn back on the 6 relationships mentioned (whihc is how it was when I first tried this). Actually probably even on does it. Now when you create the WCFMC relationship you get a circular error. 

However with the 6 relationships off i can create the WCFMC relationship then turn the other 6 on and no errors.

To me it seems to be a bug as if their is a circular dependency it must be independent of the order you create the relationships.

Mike

[v-qiuyu-msft]

---

Now turn back on the 6 relationships mentioned (whihc is how it was when I first tried this). Actually probably even on does it. Now when you create the WCFMC relationship you get a circular error. 

---

Hi @masplin,

Which kind of 6 relationships turn on then error will throws out when you create WCFMC relationship with VisitType?

Best Regards,
Qiuyun Yu

[masplin]

The 6 relationships between posted header/service header and visit type/order table/marketing source

Thereis an inactive relationship between posted header and vehicle thar wont work and i can delete. So basically turn all of them on except that one. i think if you just turn one on it will error