Ghost Rows in Power BI Report from Direct Lake Semantic Model

[jbauer22]

This is the really odd.  We are seeing what appears to be Ghost records from historical delta changes in our results. 

We intentionally created a Many to Many relationship in our star schema to join dim_gaap_mapping to fact_ledger as seen in the semantic model diagram below.  We have default filters in our measures that prevent a cartesian product, but that is not the problem.  The problem is that ghost records from fact_ledger are returned that appear to be from historical delta values, like it is doing time travel.  The only thing we can think of, is that there is a bug with a many to many relationship in direct lake mode that exposes older delta records (ghosts) to be rendered in Power BI.  Has anyone else seen this?

Semantic Model (single dim_gaap_mapping filters fact_ledger)

 

As you can see from the screenshot below, the correct value of 6,675.23 is presented when there is no slicer filter on “gaap”.

 

Once you click the slicer to choose a “gaap” up come the ghost records.  Note: These records do not appear on fact_ledger is you query the sql endpoint, only in Power BI.  There should only be one record, but you can see from row_update_dttm that is is showing the state of the ledger rows each time it was updated.  No me gusta.

Note: The many to many relationship works on a .pbix semantic model imported to the vertipaq.  It doesn’t work on a Direct Lake Semantic model to our lakehouse which further points to a bug in delta tables on the direct lake semantic model.

Note: A “gaap” row will have many “book_codes”.  Example: “US GAAP” has book_code B and U.  “Local GAAP” has book_code B and L.

We are only interested in resolving the defect.  So please only post replies specific to the delta table ghost record issue and not for a work around.  We have a work around.  We want to ensure we understand what is happening here so we don't expose this in other models.  Thanks!!

[hackcrr]

hi, @jbauer22 

Double-check the data in the source (Lakehouse) to make sure there are no spurious records in it. Use a SQL query to confirm that the records you see in Power BI do not exist in the source.
Verify that the filters you apply to your measures are working properly and not causing unexpected results. If possible, consider simplifying relationships. Although you mentioned that you want to understand the defects rather than use a solution, simplifying the model may help isolate the problem.
Use the simplified model to create a separate Power BI report to test if the issue persists with minimal data and relationships.

hackcrr

If this post helps, then please consider Accept it as the solution and kudos to this post to help the other members find it more quickly

[hackcrr]

hi, @jbauer22 

Double-check the data in the source (Lakehouse) to make sure there are no spurious records in it. Use a SQL query to confirm that the records you see in Power BI do not exist in the source.
Verify that the filters you apply to your measures are working properly and not causing unexpected results. If possible, consider simplifying relationships. Although you mentioned that you want to understand the defects rather than use a solution, simplifying the model may help isolate the problem.
Use the simplified model to create a separate Power BI report to test if the issue persists with minimal data and relationships.

hackcrr

If this post helps, then please consider Accept it as the solution and kudos to this post to help the other members find it more quickly

[jbauer22]

Thanks for the response.  We just triple checked the records in the lakehouse and confirmed those records do not exist when we query the fact_ledger table directly.  The semantic model screen shot in the original post is the simplified version to recreate the issue.  The many to many relationship seems to be causing time travel on the fact_ledger delta table.  We believe this to be a defect.