Forum Discussion
Microsoft EmployeeShare your thoughts on DirectQuery for Power BI datasets and Azure Analysis Services (preview)
Helper IHi,
So we have looked at this - having the calendar in each dataset means we cannot have any other connection between the 2 datasets, which removes the point of combining them in a composite model.
So we have a custom calendar, published as a dataset.
We have proprietary sales system data, published as a dataset.
We have Dynamics 365 CRM data, published as a dataset.
The CRM data and sales system share only custom account number, so the need is to create a 1:1 relationship between the two on these fields.
Using import mode, I could take a custom calendar and create indirect relationships to as many tables as needed, across the sales data and the CRM data. But as they are published to be used as composite, any attempt to have 1 calendar that affects more than 1 table errors, with the message I gave in my original post.
From your suggestion, I have Live connected the custom calendar in the CRM data and the sales data, then published that. But I can't connect the calendars if the account number is connected (directly or indirectly) because I get an errors that it would introduce ambiguity as due to the connection between the account numbers.
If I connect the calendars first, I cannot connect the account numbers, removing the point of having both datasets.
Is it impossible to have custom calendars that connect to more than one table across composite reports?
Are these calendars different between datasets? We had a situation where we connected 2 datasets, and each one had a calendar already with all the relationships in place. What we did in the composite dataset, is that we created a 1 to 1 relationship between the dates of the 2 calendars and hid one of them. This solved our issue.
- KeithCoxAuto
Hi Anonymous - they are indentical but I get an error trying to create the 1:1 relationship between them seemingly because of the 1:1 relationship between the account numbers across the 2 datasets. Did you datasets have any other points of connection or was it just through the dates?
That was the only 1:1 relationship between the 2 datasets in our case, yes. But we did have multiple 1:M relationships in a different composite datasets model, but then no 1:1
- KeithCoxAuto
Ah. That seems to be the issue that's 'breaking' it. Thanks for taking the time to try and help, though, I appreciate it.