RI violations - Data Lake Model

[101Mathew]

Hi all,

I'm using DAX studio onto my Data Lake model in the service - and I'm getting RI violcations (fig 1), but both the SQL dev who have checked and I'll looked for rows with blank on either side and no joy.

all relationships are 1 to many into the transactions table, all filtering single direction from the Dims into the fact table transactions (see fig 2) - I've put the dimIDs from Transactions and TransactionType together and selected blank from the Dim table (see Fig 3)

 
Is this an issue with DAX Studio, Data Lake, or is there something I'm missing?

Fig 1 blue are disconnected tables, yellow is the main date table (with 7 relationships) 

Fig 2
Model map:

Fig 3 no mismatch values between them both when blank on the dim is selected

[101Mathew]

1 - I can confirm that on all date tables, we have checked no missing items - we have checked for null on the fact table id

2 - These are all linked via DimDateIds like 19000101 etc... the date column on the date, on the table is not the linkid to the fact table however, and previously the Date column was date/time by default, this has been changed and issue RI Violations are still present.

It's worth noting that editing Direct Lake model in web and in desktop allow date hierarchies - which have been created in those enviroments

3 - These are present in each dim and link directed

4 - your talking about assume referential integrity - this is not ideal per https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-assume-referential-integrity#what-ha...
SQL data engineers cannot give me 100% on future link id issues (as we are developing) - so this could result in incorrect numbers being reported

Finally I cannot see the settting for Enable verbose logging in Dax Studio please advise

[v-dineshya]

Hi @101Mathew ,

Previously I have suggested "Verbose" option to another member  in this community. Please check below thread, to enable "Verbose" in DAX studio.

Solved: Numerous disconnects from Semantic Model - Microsoft Fabric Community

Regards,

Dinesh

[101Mathew]

Interesting, almost none of those DAX Studio steps appear.

Step 2 open Dax studio go to advance and look for start tracing or enable tracing those are not options you have import Metrics, Export Metric, View Metrics, Export Data, View As, Run Benchmark, capture Diagnostics, SQL profiler, analyse in Excel, swap delimiters

under file->options -> tab advanced you have publish functions, export functions

I did find this setting when searching, which I changed to verbose:

[v-dineshya · Accepted solution]

Hi @101Mathew ,

Please check below things.

1. Even if both columns look like integers or texts, Power BI may consider types that don’t match at the VertiPaq engine level.

Solution: In Power BI Desktop, Check both columns data types (in both Power Query and the Model view).

2. Sometimes values that look blank are actually non-printable characters or whitespace (NULLs, whitespace, or control characters).

Solution: Try this below DAX query example in DAX Studio to check.

EVALUATE
FILTER (
VALUES ( Transactions[DimTransactionTypesID] ),
NOT Transactions[DimTransactionTypesID] IN VALUES ( 'Transaction Types'[DimTransactionTypesID] )
)

Note: This returns transaction values that don’t exist in the dimension table.

or you can try below code.

EVALUATE
FILTER (
VALUES ( 'Transaction Types'[DimTransactionTypesID] ),
ISBLANK ( 'Transaction Types'[DimTransactionTypesID] )
)

3. If you are using a Data Lake model, which can behave differently with lazy loading or Hybrid Tables. Power BI may not fully materialize all dim values until queried in visuals. This means VertiPaq may think there are missing keys because the dimension table is not fully loaded.

Solution: Temporarily switch the model or that dim table to Import mode and re-check in DAX Studio. Or use KEEPFILTERS() in a test matrix to force dim values into context.

4. Even with 1:* relationships, if Transaction Types has duplicate keys, the engine can’t build a clean relationship.

Solution: Check for duplicates in DAX studio with below sample code.

EVALUATE
ADDCOLUMNS (
SUMMARIZE ( 'Transaction Types', 'Transaction Types'[DimTransactionTypesID] ),
"RowCount", COUNTROWS (
FILTER (
'Transaction Types',
'Transaction Types'[DimTransactionTypesID] = EARLIER ( 'Transaction Types'[DimTransactionTypesID] )
)
)
)

Note: Look for any with RowCount > 1.

5. If data was imported with bad keys before relationships were defined, Power BI does not clean them.

Solution: Clear and reload your tables (via Power BI Desktop refresh or Pipeline refresh with schema validation).

Regarding Verbose Logging in DAX Studio:

In option --> Logging --> select the "Logging level" to "Verbose" means, This is the equivalent of enabling verbose or diagnostic-level logs in DAX Studio there is no separate "Enable verbose logging" switch, just this dropdown.

Connect DAX Studio to a Power BI Desktop file or a Tabular model (Import/DirectQuery model). After connection, additional diagnostic tools become visible,

Start Tracing, Stop Tracing, All Queries, Server Timings, Query Plan and Session Traces (if connected to XMLA/AS). These are found in the ribbon, not under File > Options.

Note: If you open DAX Studio without connecting to a model, these runtime options are hidden. If you are trying to capture runtime query performance issues, go to View > Advanced Tab > Server Timings + Query Plan but only after connecting to a model.

Logs will typically be written to below path.

C:\Users\<username>\AppData\Local\DaxStudio\Logs

You can check this path for detailed logs after running queries.

Note: If your want to trace issues related to date hierarchy behavior or Direct Lake compatibility, try to capture a Query Trace after reproducing the issue in DAX Studio connected to your model. This may reveal differences in query execution paths (especially with Direct Lake and semantic models).

"Start Tracing" and similar options like "Performance Analyzer", "Trace Events" or "Advanced Tracing" are Only available when connected to supported engines like Power BI Desktop (Import or DirectQuery), SSAS Tabular and Azure Analysis Services. 

Not available (or partially disabled) for Direct Lake connections, Unsupported endpoints like Fabric Direct Lake SQL endpoint and Some cloud semantic models.

Note: In your case, you are connected to a model that does not support full trace or diagnostic events (likely a Direct Lake or semantic model). That’s why you only see limited tools.

You have already enabled Verbose Logging in Options > Logging, so DAX Studio is capturing the max diagnostics it's allowed to.

If you want full diagnostic options, Export your semantic model to a .pbix file. Open in Power BI Desktop (non-Fabric version). Connect DAX Studio to that Desktop instance you will get full trace options.

Please refer below snap. I have connected to DAX studio with my sample .PBIX file.

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[v-dineshya]

Hi @101Mathew ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[v-dineshya]

Hi @101Mathew ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[v-dineshya]

Hi @101Mathew ,

Thank you for reaching out to the Microsoft Community Forum.

RI violations typically occur when a fact table contains foreign key values that do not match any primary key in the related dimension table. In DAX Studio, these are flagged during model analysis, especially when using the VertiPaq Analyzer.

Please check the below things to fix the issue.

1. Ensuring star schema integrity. Avoid nulls in foreign keys. Use surrogate keys or default "Unknown" rows in dimension tables to handle unmatched records.

2. In Power BI, the Direct Lake mode has modeling constraints, No support for calculated columns or MDX hierarchies. Requirement for physical tables. Limitations on DateTime relationships. These constraints can indirectly contribute to RI violations if your model relies on unsupported features or transformations.

3. Add a Default Row, “No Match” or “Unknown” row in your dimension tables and replace nulls in the fact table with this key. This ensures every foreign key has a match, maintaining RI.

4. Check RI allows Power BI to use INNER JOINs instead of LEFT OUTER JOINs, improving performance. Sometimes values may appear non-blank but still not match due to data type mismatches or trailing spaces.

Note: Check all dimension tables have a default row (Unknown) and update the fact table to use this key where no match exists. Confirm that the data types and formats of keys match exactly between fact and dimension tables. In DAX studio, Enable verbose logging to trace query execution and pinpoint where RI violations are triggered.

Please refer the Community thread.

Solved: RI Violations due to dimension only being relevant... - Microsoft Fabric Community

If this information is helpful, please “Accept it as a solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.

Regards,

Dinesh