Normalizing model in order to get rid of measure in dimension table

[jaryszek]

Hello,

my model is:

as you can see i have a measure and to get proper results and sum Discount i need to filter table DateDiscountMeterId. 

I do not like this design, is not efficient and adding additional daxes.
My topology is Offer, PricingModel and Region and these filelds always must be in a matrix. 

So i created 2 fact tables model design like here with shared dimenstion table:

So now when i want to sum up discouny effective price i am using my topology table:

which seems fine  but how to get rid of these empty row? It is comming from Fct_EA_AmortizedCosts table, there is no record with DateDiscountPlanMeterId = 5. 

And second question - It is a valid approach and valid power bi design?
What are your thoughs? 

Please find sample models here: 
Disc Samples 

Best,
Jacek

[v-achippa]

Hi @jaryszek,

Thank you for reaching out to Microsoft Fabric Community.

The issue here the unexpected blank row in the visual is because of some id's in the shared dimension table (Dim_DateDiscountPlanMeterId) do not have corresponding rows in Fct_EA_AmortizedCosts. So when the matrix visual evaluates the measure it includes unmatched dimension rows.

• Use TREATAS to filter across fact tables, so the matrix reflects only matching rows from the base table. Please use the below measure:
  Total Discount Price = CALCULATE (
  SUM ( Fct_EA_AmortizedCosts_DiscountPlans[DiscountPlanEffectivePrice] ),
  TREATAS ( VALUES ( Fct_EA_AmortizedCosts[DateDiscountPlanMeterId] ), Fct_EA_AmortizedCosts_DiscountPlans[DateDiscountPlanMeterId] ) )

And for your second question, yes your approach of normalizing the model into two fact tables with a shared key is valid and follows the best practice dimensional modeling.

If this post helps in resolving the issue, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @jaryszek,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution I have provided for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[jaineshp]

Hey @jaryszek,

Looking at your model normalization challenge, here are my recommendations:

Addressing the Empty Row Issue

Root Cause Analysis:

• Empty rows appear because your shared dimension table (Dim_DateDiscountPlanMeterId) contains keys that don't exist in one of your fact tables
• This creates a many-to-one relationship where the "one" side has no corresponding records

Solution Options:

1. Filter at Dimension Level:
   
   CALCULATE(
   SUM(Fct_EA_AmortizedCosts[EffectivePrice]),
   FILTER(
   Dim_DateDiscountPlanMeterId,
   RELATED(Fct_EA_AmortizedCosts[DateDiscountPlanMeterId]) <> BLANK()
   )
   )
   
2. Use SUMMARIZECOLUMNS for Clean Results:
   
   Effective Price Clean =
   SUMX(
   FILTER(
   Dim_DateDiscountPlanMeterId,
   NOT ISBLANK(RELATED(Fct_EA_AmortizedCosts[DateDiscountPlanMeterId]))
   ),
   RELATED(Fct_EA_AmortizedCosts[EffectivePrice])
   )
   
   
   1. Visual-Level Filtering:
      • Add visual filter: DateDiscountPlanMeterId is not blank
      • Or use "Show items with no data" = OFF in visual settings

   Model Design Validation

3. Positive Aspects:

   • Shared dimension approach reduces redundancy
   • Maintains referential integrity
   • Supports consistent filtering across fact tables
   • Follows Kimball dimensional modeling principles

   Recommendations for Improvement:

   1. Ensure Complete Key Coverage:
      • Populate dimension table only with keys that exist in at least one fact table
      • Use UNION of distinct keys from both fact tables when building dimension
   2. Consider Bridge Table Pattern:
      
      Fact_AmortizedCosts ← Bridge_DateDiscountPlan → Fact_DiscountMetrics
      
      

   3.  Alternative: Separate Dimensions with Role-Playing:

           Keep separate date/discount dimensions
           Use role-playing relationships for shared attributes
      
      

      Best Practice Implementation

      Data Model Optimization:

      • Implement proper data lineage in your ETL process
      • Ensure dimension keys are consistently generated
      • Add data quality checks to prevent orphaned records

      DAX Pattern for Clean Measures:
      
      Total Effective Price =
      VAR ValidKeys =
      FILTER(
      Dim_DateDiscountPlanMeterId,
      NOT ISBLANK(RELATED(Fct_EA_AmortizedCosts[DateDiscountPlanMeterId]))
      )
      RETURN
      SUMX(ValidKeys, RELATED(Fct_EA_AmortizedCosts[EffectivePrice]))
      

[jaryszek · Accepted solution]

ok so what will be a difference here?

Fact_AmortizedCosts ← Bridge_DateDiscountPlan → Fact_DiscountMetrics

plus , please , do not generate this answers in chat gpt mostly. i am not buying them.