Multiple Relationship Between Data Tables

[Vicky010598]

I'm new to Power BI, and I'm encountering a challenge when creating multiple relationships in my dashboard. Here's the scenario:

• Dashboard Setup: I have six card visuals displaying customer counts.
• Data Source: The data for these cards comes from four different tables.
• Card Data Breakdown:
  • Cards 1 & 2: Data from the same table using a static date.
  • Card 3: Data from a different table using a "created_date" column.
  • Cards 4 & 5: Data from another table using an "updated_date" column.
  • Card 6: Data from yet another table using Bank_send_date.
• Date Handling:
  • I've created a DAX expression DateOnly to unify the date format across all tables and this is that DAX function 
    DateOnly = DATE(YEAR('Table'[Date_Column]), MONTH('Table'[Date_Column]), DAY('Table'[Date_Column])
  • This expression extracts the year, month, and day from the respective date columns.
• Initial Relationships:
  • I've established many-to-many relationships between all tables using the common DateOnly column.
  • This allows the date slicer to filter all card counts effectively.

New Requirement:

• Additional Table: I want to introduce another table with three slicers based on different columns. The table name is Appsflyer Data.
• Relationship Challenge:
  • The existing many-to-many relationship on DateOnly prevents creating another relationship with this new table.
• Bridge Table Attempt:
  • I tried creating a bridge table with Appsflyer_id to connect the new table to the fact tables.
  • However, the slicers from the new table only affect data from the fact table, not the other cards.

Question:

I'm unsure how to proceed. How can I enable the new table's slicers to filter data across all cards in my dashboard?

[Vicky010598]

Thank you for your input @Anonymous . I initially tried creating a Calendar table and establishing a relationship with it to ensure accurate counts on my dashboard. However, I encountered an issue because I had already established a relationship with the Date column using the Calendar table. Now, I'm trying to incorporate another table containing additional columns for which I want to create slicers. Since I already have a relationship with the Calendar table, I can't establish another one. I attempted to resolve this by creating a bridge table and placing all the slicer data there, connecting it to the Fact table. However, this approach only affected the data for the Fact table card, leaving the data for other table cards unchanged. Do you have any alternative solutions that might address this challenge? Your insights would be greatly appreciated. Thank you in advance.

[Anonymous]

If all your relationship use a date as the connecting column, it would probably be a good idea to create a calendar table. This table would let you set 1 to many relationships to the rest of the tables, removing all those many to many relationships.

Creating a simple calendar table is as easy as using this formula after selecting the  "New Table" option in the Modeling menu (you can make more complex calendars by adding more columns or conditions): 

calendar = CALENDAR(date(2024,01,01), today())

Once you do that it should be a lot easier to create new tables and relationships.