Forum Discussion
Sharepoint and SQL Many to One Relation Problem in Composite Feature (Preview)
Hi everybody, I have a problem trying to build a report that shows SLAs for our Ticketsystem.
I have the following SQL tables: / sharepoint online lists
I use the composite preview feature, the SQL querys are direct query and the sharepoint is imported mode.
Sharepoint:
Customer
- ID (unique)
- Title (unique)
SLA
- CustomerID
- Value (i.e. 10)
- Unit (i.e. minutes)
- SLATypesID
SLATypes
- ID
- Type (i.e. reactiontime priority 1)
And in SQL I have a list of tickets
Tickets
- ID
- CustomerName (Is equal to the title of the customer sharepoint list)
- Reaction Time
The Relations that PowerBI detects is:
Tickets *-* Customer 1-* SLA *-1 SLATypes
With this data I wanted to have a SLA Report, so for each ticket calculate the SLA that should take account depending on the priority and the customer name of each ticket.
Here are my problems with that:
1. In the Customer sharepoint list I had set the Customer Title attribute to be unique, so the relation should be 1-* and not *-*?
Both of these Columns are Text. I even copied the list to excel and did the "demove duplicates" trick but it shows no duplicates..
Can someone explane why there could be a relationship 1-* be made?
2. I can relate Tickets to Customers and I can relate Customs to SLAs but I cant relate Tickets to SLA.
3. I thought of another workaround which does not use relations but for each ticket calculate the needed SLA based on CALCULATE or LOOKUPVALUE function but these seem not to work because of the direct query mode. Do you know any alternatives?
Thank you for your help!
best regards
Duffkess
Anonymous,
This behavior is by design in mixed storage mode. I got the following response from PG:
"By design, cross island relationship is always Many to many for now. For GA of composite model, we will allow a many to one cross island, but even that would still have some restrictions and wont support everything a same source many to one does."
There is a simiar thread for your reference.
https://community.powerbi.com/t5/Issues/Composite-Models-Date-Table-incorrectly-creates-many-to-many/idi-p/488312
Regards,
Lydia
4 Replies
Anonymous,
This behavior is by design in mixed storage mode. I got the following response from PG:
"By design, cross island relationship is always Many to many for now. For GA of composite model, we will allow a many to one cross island, but even that would still have some restrictions and wont support everything a same source many to one does."
There is a simiar thread for your reference.
https://community.powerbi.com/t5/Issues/Composite-Models-Date-Table-incorrectly-creates-many-to-many/idi-p/488312
Regards,
LydiaCan you edit the relationship to be 1-*?
There is a possibility where capitalization is causing SharePoint to think that a value is unique but not Power BI. Tough to say, that's a long shot. Any chance you can share data?
I cant edit the relationship, it just says: "The cardinality you selected isn't valid for this relationship".
I read about the problem with case sensivity, but currently im testing with 7 different customers where every value in the SP list is unique. I even used the "Remove Duplicates" in the PowerBI Query to archive unique values, but still I got same problem.
Can you turn off Composite Models in your Preview Features? That will prevent a *-* from being created. Then see what happens. Wonder if there might be some kind of bug.
You could always try to create an Enter Data query and enter your customers manually and try it, this might help track down if it is some weird issue with your query or something.
