Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Uniqueidentifiers not recognize as valid key in mixed mode relationships

I am working on a SQL server where the primary/foreign keys majority in the Uniqueidentifier data type. Power BI recognize this as a text field (though, MS Access will recognize as an integer/GUID field)...not sure if that is important to note or not, but it is interesting that Power BI doesn't recognize the data type as SQL Server and Access recognize it.

 

So, when using these keys in relationships where all tables are in DirectQuery or all in Import mode, Power BI recognizes the relationships and matches the keys correctly as values aggregate up as expected. BUT when I start adding in Dual mode with a mixture of DirectQuery/Import mode tables to utilize the Aggregate feature, the keys are no longer recognized and when using the visuals, it assumes there are no matches and aggregate everything up to a blank value.

 

I think this is due to how Power BI recognizes Uniqueidentifiers as mentioned above. There's other issues related to the Aggregate feature that I noticed someone else made a post to (i.e. Power BI ignore Aggregate tables even though the measure's filters within the Calculate function still fall in line with for hitting the Aggregate table).

Status: New
Comments
v-qiuyu-msft
Community Support

Hi @vhoang149

 

In Power BI Query Editor, you can change data type for the primary/foreign key columns. 

 

q4.PNG

 

For your issue, I would suggest you go to Model view, check if the columns used in visuals have active relationships. See: https://docs.microsoft.com/en-us/power-bi/desktop-create-and-manage-relationships

 

Best Regards,
Qiuyun Yu 

vhoang149
Regular Visitor

Hi @v-qiuyu-msft ,

 

Thank you. There is no GUID option. So, when the key is like

E602F671-4DFB-4CA7-B75C-0000EFF6AF13

 

Power BI thinks it is a text column. Finally figured out that I have to transform the column type to a different data type and manually update the step to set the type to GUID.type to get it to recognize it correctly in mix mode. Why isn't GUID/Uniqueidentifier a listed option or recognized right away considering some databases have those as their keys?

Mixednuts
Helper II

I am not sure whether or not this is relevent in your situation, however I have noticed that there is a difference in how the GUID is returned between DirectQuery and Import. This would cause problems for linking I would have thought.

 

Both methods convert the UniqueIdentifier datatype in SQLServer to Text in Power BI

 

However when the same table is queried via Direct Query the GUID is returned surrounded by braces {}, eg. {8B8B9F7B-21A8-4C87-02DA-08D74D01C96B}.

 

When Import is used the GUID is returned without braces, eg 8B8B9F7B-21A8-4C87-02DA-08D74D01C96B

 

No idea why....?