Circular Relationship between tables

[derromma]

Hello,

I am facing an issue with a circular relationsip and don't know what would be the smartest way to approach this.

Let me explain:

I want so build a report, which let's me analyze the success of marketing campaigns, where customers were contacted directly.

Each customer was assigned a 'Customer-Tag', relating them to a marketing campaign. Customers can be in multiple Marketing Campaings. The Campaigns are all throughout the year.

My goal is, to be able to choose a single campaign in a slicer, and have the report automatically filtered to 1. the respective Date Range of that campaign, and 2. the respective customers that were contacted with this campaign. The report would then show information regarding Sales and Margin on customer and product level.

I was trying to solve this task like the below picture:

My plan was to have a table with campaign information ('Aktionen_Info') which would on the one side link to a table ('Aktionen_Datum'), containing all Dates where the campaign was active and on the other side link to all customers that have been contacted. These two tables in return would link to the transactions table, so that I am able to filter the transactions on both Date and customers with just one Slicer.

This does not work unfortunately, as I am getting empty/zero values in the report for e.g. Sales when choosing a specific campaing in a slicer.

I would like to avoid using inactive relationships, since I would have to add the 'Use Relationship' Function to every measure if I understood it correctly. However I am open to any solution you might be able to provide!

I hope you can understand my problem, if not I'm happy to explain more.

Many thanks!

[Anonymous]

Hi, @derromma 

Thanks for vanessafvg's reply. Have you currently resolved this problem? The cause of circular dependencies is usually cross-references, you can share the pbix file without sensitive data for testing, which will help you solve the problem faster.

Here are some notes:
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Best Regards,
Yang
Community Support Team

[vanessafvg]

circular relationships are generally caused by calculated columns.  Do you have a calculated column?  If you do, dax is the worst place to create calculated columns.  I would only create it there if you absolutely have to.  If posssible to create in power query I would do it there, that will make the circular relationship isssue go away.