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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
jbauer22
Advocate I
Advocate I

Ghost Rows in Power BI Report from Direct Lake Semantic Model

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)

semantic ghost.PNG

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”.

good amount.PNG

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.

ghost amounts.PNG

 

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!!

3 REPLIES 3
hackcrr
Super User
Super User

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
Super User
Super User

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

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.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.