Connecting 2 different sums in Dim and Fct Table between Hierarchy

[jaryszek]

Hello Guys,

I have Fct_Amortized_Cost table and Dim_EA_SavingsPlans. 

They are connected with 1 to many relationship like here:

the issue is that i am trying to use hierarchy : MeterCategory--> MeterSubcategory from Fct table and show CommitmentAmount per day: 

  ROUND(sp.Commitment_Amount * 24, 2) AS "Commitment_Amount_Per_Day",

this is sql for it. CommitmentAmount is in Dim table. 

the issue is that i am not seeing proper values when building Matrix visual. 


I have build a dax:

Commitment_Amount_Per_Day = 
ROUND(SUM(Dim_EA_SavingPlans[Commitment_Amount]) * 24, 2)

but when i am doing matrix i am not seeing proper results. 

Core Issue: No filter context propagation

Your matrix uses MeterCategory, MeterSubCategory, and potentially ResourceType — these fields:
Come from other tables (not Dim_EA_SavingPlans)
Are not directly related to Dim_EA_SavingPlans
Therefore, do not filter Dim_EA_SavingPlans, even if they are part of a visual

So SUM(Dim_EA_SavingPlans[Commitment_Amount]) just adds up everything from the entire table on every row — unless a DisplayName or ID from Dim_EA_SavingPlans is in the visual.

this is explanation from chatgpt. 

But how to make this properly? 

Best,
Jacek

[jaryszek]

One more question: 

why i should put Commitment Amount in fact table, not in dim table? Where it matters? 

Best, 
Jacek

[bhanu_gautam · Accepted solution]

@jaryszek 

Nature of Data:

Fact Tables: These tables store quantitative data for analysis, such as sales amounts, quantities, and other measurable metrics. They are typically large and contain transactional data.
Dimension Tables: These tables store descriptive attributes related to the facts, such as product names, categories, dates, and other contextual information. They are usually smaller and contain reference data.
Aggregation and Analysis:

Fact Tables: Since fact tables contain measurable data, they are designed for aggregation and analysis. Measures like Commitment_Amount are better suited in fact tables because they can be easily summed, averaged, or otherwise aggregated.
Dimension Tables: Dimension tables are not typically used for aggregation. They provide context to the facts and are used to filter, group, and categorize the data in fact tables.
Filter Context Propagation:

Fact Tables: Placing Commitment_Amount in the fact table ensures that it is correctly aggregated and filtered based on the related dimensions. This helps in maintaining the correct filter context when performing calculations.
Dimension Tables: If Commitment_Amount is placed in a dimension table, it may not propagate filters correctly, leading to incorrect aggregations and analysis results.
In your specific case, placing Commitment_Amount in the fact table would simplify calculations and ensure that the filter context is correctly applied when using DAX measures.

[jaryszek]

thank you @bhanu_gautam 

[jaryszek]

Ok I figured out that changing cross-filter direction to both from fct_table into dim_table is solving the case:

But this is a good practice? 

Best,
Jacek

[bhanu_gautam]

@jaryszek It can make the data model more complex and harder to understand, as it introduces additional paths for filter propagation which can lead to unexpected results if not carefully managed.

Bidirectional cross-filtering is useful in scenarios where you need to filter data in both directions, such as in many-to-many relationships or when creating complex calculations that require context from both tables.

Before opting for bidirectional cross-filtering, consider if there are alternative solutions such as:

Using DAX functions like RELATED or RELATEDTABLE to explicitly manage relationships within your measures.
Creating calculated columns or tables that pre-aggregate or pre-calculate necessary values.

[bhanu_gautam]

@jaryszek Try using related or relatedtable

dax
Commitment_Amount_Per_Day =
VAR CommitmentAmount =
SUMX(
RELATEDTABLE(Dim_EA_SavingsPlans),
Dim_EA_SavingsPlans[Commitment_Amount]
)
RETURN
ROUND(CommitmentAmount * 24, 2)