Filter Measure of dimensional table with another dimensional table

[Schmiber]

Hi! My model looks like this:

My fact table contains insurance claims and my dimension tables, for example, customer (D_Person), vehicle (D_Vehicle), contract (D_Coverage) data (and some more).

Suppose I want to calculate the total premia. The column premium is in the dimension table D_Coverage. To do this I define the measure: "Premia_sum = SUM(D_COVERAGE[premium])" This works for the overall sum but let's assume I want to see only the premium a certain agent (in D_AGENT) acquired. The filter wont work because premium is part of another dimension table and not the fact table. D_COVERAGE is a dimension table because I have one row per contract with one individual premium, whereas I could have multiple claims per contract (F_CLAIM). I tried to change the directions of the filters to bi-directional and it seemed to work but I want to avoid this if possible and stick to a classical star/snowflake schema because as far as I know using bi-directional relationships is not recommended. How can I do this? Treat the contract table D_COVERAGE as a fact table even though it is only one row per contract? Thanks.

[Kedar_Pande]

Since you want to filter premiums based on an agent, but the premium exists in the D_COVERAGE dimension table and not in the fact table (F_CLAIM), you can create a DAX measure that uses the relationship indirectly via the F_CLAIM fact table.

Create a Measure

Premia_By_Agent =
CALCULATE(
SUM(D_COVERAGE[premium]),
TREATAS(
VALUES(F_CLAIM[AGENT_ID]), -- This should be the column linking the agent to claims
D_AGENT[AGENT_ID] -- This is the agent ID column from your agent table
)
)

[dharmendars007]

Hello @Schmiber , 

To ensure that each claim can be linked with the respective premium, create a calculated column in the F_Claim table that pulls the premium value from D_coverage.

Claim_Premium = RELATED(D_COVERAGE[Premium])

Now that you have the premium available in F_Claim you can create a measure that sums up the premium filtered by the agent

Agent_Premium_Sum =

CALCULATE(SUM(F_CLAIM[Claim_Premium]),RELATED(D_AGENT[Agent_ID]))

This measure will aggregate the total premium associated with each agent based on the F_Claim table, ensuring that the relationships flow correctly without needing to create a bi-directional filter

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

Thank You

Dharmendar S

LinkedIN