Ambiguous paths between copies of the same table using live connection to dataset

[derekrp84]

I'm using the preview feature "DirectQuery for PBI datasets and AS" and am needing some help figuring out if there is a way to fix an error I am receiving when using measures.

Background:

I am working with extremely large tables that take the visualizations longer than what my users would like when using Direct Query against the database, so I've set up a process to incrementally refresh the tables into our Power BI Service P1 capacity. I had two copies of the same table using direct query against the database itself and there was no issue, but now that I am connecting to them in the service I am getting an ambiguous path error.

Problem:

I'm needing to be able to associate a business date to the sales for current year as well as previous years, potentially going back four years. My solution was to have a date table that defined the prior year dates for each calendar date, then join them with the current year's table joined on "CALENDAR_DATE" => "_Business Date" and the last year's table joined on "CALENDAR_DATE_LY" => "_Business Date", and so on as detailed in the images below so that I can show year over year trend lines, matrices, etc.

Relationships:

Using the columns directly works fine:

However, my issue comes when I create a measure that uses one of those tables, such as "POS Sales LY Test = SUM('Sales LY'[_POS Sales LY])" and is placed in the same visual as CALENDAR_DATE. It then gives this error (identifying information redacted due to privacy issues, but it shows the same name in all three redacted areas):

Error Message text (instances where I have "[REDACTED WORKSPACE NAME]" are all referencing the same workspace and "[REDACTED TABLE NAME]" all refer to the same table):

There are ambiguous paths between '{"protocol":"analysis-services","address":{"server":"powerbi://api.powerbi.com/v1.0/myorg/[REDACTED WORKSPACE NAME]","database":"[REDACTED TABLE NAME]"},"authentication":null,"query":null}' and 'Dates - All Needed':

'{"protocol":"analysis-services","address":{"server":"powerbi://api.powerbi.com/v1.0/myorg/[REDACTED WORKSPACE NAME]","database":"[REDACTED TABLE NAME]"},"authentication":null,"query":null}'->'Dates - All Needed'
and
'{"protocol":"analysis-services","address":{"server":"powerbi://api.powerbi.com/v1.0/myorg/[REDACTED WORKSPACE NAME]","database":"[REDACTED TABLE NAME]"},"authentication":null,"query":null}'->'Dates - All Needed'

Additional details (Server and Database are the same for both copies of the table):

I was hoping that Power BI would treat the two copies of the table as separate tables like it did when using direct query against the database, however it looks like Power BI is treating them as two ways to get to the same table when they are imported to the service. Is there any way to fix this, is this a bug due to it still being a preview feature, or will I need to explore alternatives like pulling a different copy of the table into the service for each year going back?

As an alternative, if there is a way in DAX to create the prior year measures that would correlate them to the CALENDAR_DATE that would work too. I've seen other solutions that use DAX's time functions to solve this, but that won't work for the data that I'm working with since the prior year dates don't correlate as normal due the financial year not aligning to standard dates. My dates are correlated by week number and day number of the year starting at different dates each year. My dates table is entirely under my control, so I can add offsets for literally anything if needed. I currently have offsets for week number, year number, etc., and day of week is just 1-7 for the different days of the week.

Example for how the offsets work to correlate the dates:

CALENDAR_DATE with week_offset of 0 and day_of_week as 1 correlates to CALENDAR_DATE_LY with week_offset of -52 and day_of_week as 1, and so on subtracting 52 for each year going back.

Thank you in advance for any assistance.

[v-stephen-msft]

Hi @derekrp84 ,

Nice workaround you provided.

Or try to change your relationships to make it work.

Please refer to

Solved: Ambiguous paths between tables connection - Microsoft Power BI Community

Tabular 2016 Bidirectional Relationship (microsoft.com)

Solved: Ambiguous paths among tables! 😞 - Microsoft Power BI Community

Bidirectional relationships and ambiguity in DAX - SQLBI

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[derekrp84 · Accepted solution]

As usually happens when I've asked for help on something in my career, I just figured out a work-around. I can create a new dataset starting with a dummy table that contains nothing so that it creates a dataset then direct connecting to the dataset/table and publishing it up, then connecting to that dataset. So basically I'm live connecting to a dataset that live connects to another dataset. Not optimal, but it seems to work. Any alternatives would be welcome.