Filter for customers with single product

[MarkEden]

Hi All,

Im struggling with what seems like it should be very simple. I have the below chart that shows insurance customers segmented by premium bands. I need to add a filter to this so that it includes only customers who have 1 policy. 

I have verified the total accounts and total placements figures are correct however if I add the numbers in the chart they total approx 1,500 which is obviously incorrect. 

There can be multiple transactions (and so rows of data) per policy so I believe it is calculating based on transactions so need to find a way to group transactions into a single policy. I've tried versions of summarize, groupby, true/false but can't seem to get it right.

My data model has a transaction fact table and policy type dim table connected by a one to many relationship.

Any guidance would be highly appreciated.

[danextian · Accepted solution]

Hi @MarkEden ,

You can count how many policy a customer has using a formula similar to below.

=
CALCULATE (
    DISTINCTCOUNT ( 'table'[policy] ),
    ALLEXCEPT ( 'table', 'table'[customer] )
)

Instead of counting the transactions, count the unique policy.

[MarkEden]

@danextian thank you. I needed to tweak it slightly to fit the x axis category but it got it working. Appreciate your help