Forum Discussion

schilders's avatar
schilders
Helper I
1 year ago

Data Model Does Not Return Accurate Summary

I'm working with the data model as shown above.  My intent was to design a star schema model.  The Member Months object uses a Merge Key that concatenates several fields to achieve a primary key.  This Merge Key has a one to many relationship with the Details object using the same Merge Key pattern.

 

Business Rules:

1.  Member Months is an aggregate per year, per month, per provider, per health plan.  Every member is represented in Member Months.

2.  Details is an SQL query, like Member Months, that represent all claims that have a date of service in the last 5 years.  

3.  Not every member will have a claim in a given year.  

 

For a given month and year, my validation query is returning 39,400 member months.  The visual sums Member Months for the same timeframe and returns 37,547 member months.  As such, validation fails because the visual does not return the same result as the SQL query.

I'm using a series of slicers to filter the results.  The DimDate object contains a hierarchy which drives the slicer.  DimDate has a one to many relationship with Details based on the DimDate's Date field and the Date of Service field in Details.

 

Questions:

1.  What must I do to ensure validation is successful?

2.  Are there resources that describe how the data model in Power BI filters results, specifically strategies to ensure / prevent under- or over-reporting of results?

 

Thank you in advance for your help and guidance.

3 Replies

  • schilders hmmm it is hard to tell without looking at the pbix file. It will be easier if you share pbix file using one drive/google drive with the expected output. Remove any sensitive information before sharing.

  • schilders the one thing in the model I noticed is that you have cross-filter direction set to both for a few of the tables which is not a recommended/best practice. Could you change the filter direction to single for these relationships and then test the results?

    • schilders's avatar
      schilders
      Helper I

      parry2k Thanks for your response!  I changed all filter directions to single.  This grossly inflated the Member Months aggregate.  Reverting back to Both between Member Months and Details restores my under-stated value.  

      This is an excellent point.  I'm refactoring an older dashboard and its data model implemented cross filtering in both directions.  

      Please let me know if there are other changes I should attempt.

       

      Appreciate your help and guidance.