cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Duffkess Regular Visitor
Regular Visitor

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: Sharepoint and SQL Many to One Relation Problem in Composite Feature (Preview)

@Duffkess,

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...

Regards,
Lydia


Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Super User
Super User

Re: Sharepoint and SQL Many to One Relation Problem in Composite Feature (Preview)

Can 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? 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Duffkess Regular Visitor
Regular Visitor

Re: Sharepoint and SQL Many to One Relation Problem in Composite Feature (Preview)

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.

 

Super User
Super User

Re: Sharepoint and SQL Many to One Relation Problem in Composite Feature (Preview)

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Moderator v-yuezhe-msft
Moderator

Re: Sharepoint and SQL Many to One Relation Problem in Composite Feature (Preview)

@Duffkess,

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...

Regards,
Lydia


Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.