Assume referential Integrity

[Anonymous]

Hi

I am trying to build a model, but on some of the relationship joins it will not let me set the Assume RI, because it is disabled, although I do know there is RI between these tables as one is a Foreign Key to another, can any one help.  I can set RI for 3 relationships but not for all of them and when I try to pull a field into my Visualisation from one of the none RI set tables, I am getting an error saying there are too many records

Just started using Power BI, but do have experience of other toolsets

thanks

Alison

[v-cherch-msft]

Hi @Anonymous 

I would suggest you have a look at below articles.If you need further help,please share the sample file and your expected output.You can upload it to OneDrive and post the link here.Do mask sensitive data before uploading.

https://docs.microsoft.com/en-us/power-bi/desktop-assume-referential-integrity

https://docs.microsoft.com/en-us/power-bi/desktop-create-and-manage-relationships

Regards,

[Anonymous]

Hi

If I run the following

select * from GL_F_JOURNAL -- fact
where not exists
(select * from D_AC_Analysis a -- Dimension
where a.AC_Analysis_skey = gl_f_JOURNAL.Account_Suffix_Skey)

It returns no records, and there is a foreign key between the fact table and the dimension table so in my mind, Assume referential Integrity should allow me to set it 

where am I going wrong, is there a limit to the number of relationships you can set this on ?

[v-cherch-msft]

Hi @Anonymous 

You can use the Assume Referential Integrity selection to enable running more efficient queries against your data source. This feature has a few requirements of the underlying data, and it is only available when using DirectQuery.

For further,please have a look at the article:https://docs.microsoft.com/en-us/power-bi/desktop-assume-referential-integrity

Please check the Requirements for using Assume referential integrity.

Regards,