Calculated Table - Many To Many issue

[MrToast20]

Hi there,

I have tables in powerbi as follows

Premiums Table

Amount	Report Date	Category
1200.00	31/07/2021	HAULAGE
500.00	30/06/2021	TAXI
2356.00	31/07/2021	VAN

Claims Table

Amount Paid	Amount OS	Report Date	Category
0	0	31/07/2021	TAXI
100	1000	31/07/2021	HAULAGE
5000	0	30/06/2021	HAULAGE

Category Table

TAXI

VAN

HAULAGE

The claims and Premium table have a 1 to many relations with the Category table

I have created a new table to obtain unique dates using the follwing DAX

Report_Dates = CALCULATETABLE ( DISTINCT ( Premium[Report_Date]))

When I try to create a relaship between Report_Dates and Premiums and/or claims tables I get a many to many warning. 

The report dates table now contains unique values so I am not expecting a many to many error.  

Any ideas what I am missing here?

[v-shex-msft]

HI @MrToast20,

AFAIK, current power bi data model allows to active one major relationships of these relationships will cause a conflict of power bi analysis/aggregations between these records.

Relationships in analysis services tabular models  

In addition, I'd like to suggest you take a look at the following link about star schema relationship designs if helps:

Understand star schema and the importance for Power BI - Power BI | Microsoft Docs
Regards,

Xiaoxin Sheng

[PaulDBrown · Accepted solution]

You should create a calendar table with unique consecutive date values covering the range of dates in your model and join in a 1-many relationship with the premium and claims table. You the use the calendar table in your measures, filters, slicers etc...

[selimovd]

Hey @MrToast20 ,

how does the table look? Are there multiple values?

Why don't you just create a dimensional date table with the CALENDAR function?

Check here how to do that:

https://softcrylic.com/blogs/power-bi-for-beginners-how-to-create-a-date-table-in-power-bi/

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic