Power BI Data Modeling Issue.

[powerbicrack]

I am working with Power BI to model data related to boats, their operators, and insurer portfolios. My challenge is correctly managing relationships when insurers underwrite risks at both the group and subsidiary (individual operator) levels. Below is an overview of my data structure:

Data Structure

Boats Table (Contains yearly data for each boat)

BoatIDYearValueDeparturesOperatorOperatorGroup

B001	2020	1.5M	100	OpA	GroupX
B002	2020	2.0M	200	OpB	GroupX
B003	2021	3.2M	150	OpC	GroupY

Insurer Portfolio Table (Shows which operators an insurer underwrites each year)

Insurer	Year	Operator	OperatorGroup	% Line
InsX	2020	OpA	GroupX	50%
InsX	2020	OpB	GroupX	50%
InsY	2021	OpC	GroupY	100%
InsZ	2021	OpD	GroupZ	75%

Bridge Tables Used for Relationships

To establish relationships, I created two bridge tables:

1. Bridge Table 1: Connects boats to insured groups.

   • Fields: Year, InsuredGroup

   • Example:

     Year	InsuredGroup
     2020	GroupX
     2021	GroupY

2. Bridge Table 2: Connects insurers to insured groups instead of individual operators.

   • Fields: Year, Client, InsuredGroup

   • Example:

     Year	Client	InsuredGroup
     2020	InsX	GroupX
     2021	InsY	GroupY

Issue Faced

Some insurers underwrite risks at the group level (e.g., GroupX), while others underwrite risks at the individual operator level (e.g., OpA, OpB). This creates a challenge in data modeling because:

• There are cases where an insurer has only underwritten some subsidiaries of a group but not all.

• My current relationship model through bridge tables does not fully capture cases where an insurer’s portfolio is mixed (some at group level, some at individual operator level).

• When aggregating data, I need to ensure that if an insurer has underwritten GroupX, it includes all operators under that group, whereas if they have underwritten OpA only, it does not assume coverage for OpB within GroupX.

• The % Line column in the Insurer Portfolio Table represents the proportion of coverage an insurer has underwritten for a particular operatorandusedtocalculateexposuresofboatoperators

[DataNinja777]

Hi @powerbicrack ,

To address the challenge of modeling insurer underwriting at both the group and subsidiary levels, the key is refining the bridge table approach to explicitly map all u

nderwriting relationships. The issue arises because some insurers underwrite at the group level, meaning they cover all operators under that group, while others underwrite only specific operators. The existing bridge tables do not fully capture cases where an insurer’s portfolio includes both group-level and operator-level underwriting, leading to incorrect aggregations when analyzing insurer exposures.

A refined solution involves creating a single comprehensive bridge table that explicitly links each insurer to the operators they underwrite. This table must account for insurers that underwrite at the group level by ensuring that all subsidiaries within that group are listed separately, while also maintaining individual operator underwriting when applicable. The structure of this bridge table should include the year, insurer, operator, operator group, and the percentage line (% Line). For example:

Year	Insurer	Operator	OperatorGroup	% Line
2020	InsX	OpA	GroupX	50%
2020	InsX	OpB	GroupX	50%
2021	InsY	OpC	GroupY	100%
2021	InsZ	OpD	GroupZ	75%

This table explicitly connects each insurer to the operators they underwrite, ensuring that if an insurer underwrites at the group level, all relevant operators are included. Once this bridge table is established, it should be connected in Power BI by relating the Boats Table to the Bridge Table on Operator and OperatorGroup (many-to-many), and linking the Insurer Portfolio Table to the Bridge Table on Insurer, Operator, and OperatorGroup (many-to-many). This setup ensures that boats are correctly mapped to their respective operators and groups, while insurers are mapped to their underwriting coverage.

With the relationships established, the next step is to create a DAX measure to compute the total exposure per insurer, ensuring that the % Line is properly applied based on the underwriting level. The following DAX measure calculates the total exposure for each insurer by looking up the boat’s value and applying the insurer’s underwriting percentage:

Total Exposure = 
SUMX(
    'Bridge Table', 
    VAR BoatExposure = LOOKUPVALUE('Boats Table'[Value], 'Boats Table'[Operator], 'Bridge Table'[Operator], 'Boats Table'[Year], 'Bridge Table'[Year])
    VAR AdjustedExposure = BoatExposure * 'Bridge Table'[% Line]
    RETURN AdjustedExposure
)

This measure iterates over the bridge table, retrieves the corresponding boat value from the Boats Table based on the Operator and Year, and applies the % Line value to calculate the insurer’s proportion of the risk. Summing these adjusted values provides the total exposure for each insurer.

By implementing this refined model, the Power BI report will correctly aggregate insurer exposure, ensuring that if an insurer has underwritten a group, all subsidiaries under that group are included in the calculations, while insurers that underwrite only specific operators do not mistakenly cover others within the same group. The % Line column is now correctly assigned, providing an accurate reflection of each insurer’s underwriting share.

Best regards,