Forum Discussion

Schmiber's avatar
Schmiber
New Member
1 year ago

Filter Measure of dimensional table with another dimensional table

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.

Need Help

2 Replies

  • dharmendars007's avatar
    dharmendars007
    Icon for Memorable Member rankMemorable Member
    1 year ago

    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 

     

     

  • Kedar_Pande's avatar
    Kedar_Pande
    Icon for Super User rankSuper User
    1 year ago

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