RI Violation removals from Fact table

[Keegan_Patton]

Good morning PBI community,

I have run into a bit of an issue regarding some RI violations that are rampant in one of my models. I have around 800 missing keys from my Dimension table that are present in my Fact. I do not want to add new keys to the Dim table, I need to remove these completely from the Fact.  When referencing the EnterpriseDna tutorial article regarding this it states,

"To fix the violations, go back to the sample file and click Transform Data. Next, go to the DimCustomer table, get the full list, and then click Apply."

I am not sure what to make of this, 'get the full list' seems somewhat vague to me. I have my violations readily available for removal but am not sure the implementation for this change. Can this be done with M?

What methods have you employed for removing missing keys in bulk?

Thank you for your help.

[v-binbinyu-msft]

Hi @Keegan_Patton ,

Please try below steps:

1. below is my test table

Dim:

Fact:

2. Please  create a new table with below dax formula

Table =
VAR tmp1 = VALUES ( Dim[Name] ) RETURN FILTER ( ALL ( 'Fact' ), [Name] IN tmp1 )

Please refer the attached .pbix file.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Keegan_Patton]

Thank you for the reply,

is the purpse of the expression below to identify the missing keys and extract them into their own table?

I am more concerned with how to either add or remove the keys in violations from dim or fact.

Is this possible?

Table =
VAR tmp1 = VALUES ( Dim[Name] ) RETURN FILTER ( ALL ( 'Fact' ), [Name] IN tmp1 )

[v-binbinyu-msft]

Hi @Keegan_Patton ,

This dax formula calculate logic is: create a new table, the condition is that the name exist in Fact[Name] and Dim[Name].

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Keegan_Patton]

Thank you for your reply, I understand the logic that is happening, but is there a process to remove the missing keys from the fact or add them to the dimension in power query?

That is the issue I am unsure of a solution for. I have no issue identifying the violations, it is dealing with them that is unclear.