How to make relation between two tables with two fields

[Anonymous]

Hi all, I have two Main tables Say "B" and "C" both have random dates data with different data. i.e Many to Many

So I have created a Date Table called "A" which have Unqiue Dates from 2018 to 2022.

I am trying to use a Date slicer as shown in below image ,but the Table C not getting filtered w.r.t dates selected from "A"

Screenshot :

I am not able to filter C table data as B&C are already in relationship with "MUID" field. how can I make double relational.

One for Date and other for MUID.

Screenshot two gives the data structure demo & the outputs which I am expecting for date slicer : 

[v-jingzhang · Accepted solution]

Hello @skondi ,

The first solution has provided specific steps that you can take to modify relationships. After that, if’d You want to display filtered items in a visual, you may need to create measures to combine the result or count them or others because there may be more than one item on the same day and with the same MUID that cannot be displayed directly in some visuals. Take the following steps for a reference.

All Data =
VAR __selectDate = SELECTEDVALUE('Calendar'[Date])
VAR __selectMUID = SELECTEDVALUE(MUID[MUID])
VAR __allData = CALCULATETABLE(DISTINCT(B[Data]), B[Datekey] = __selectDate, B[MUID] = __selectMUID)
RETURN
CONCATENATEX(__allData,B[Data],",")

All Type =
VAR __selectDate = SELECTEDVALUE('Calendar'[Date])
VAR __selectMUID = SELECTEDVALUE(MUID[MUID])
VAR __allType = CALCULATETABLE(DISTINCT(C[Type]), C[Datekey] = __selectDate, C[MUID] = __selectMUID)
RETURN
CONCATENATEX(__allType,C[Type],",")

Best regards

Community Support Team _ Jing Zhang

If this post helps,please consider Accepting it as the solution to help other members find it.

[amitchandak]

@Anonymous , Table A to B or C can not be Many to Many.

Create a date table using a calendar.  Join with date column of B and C , should be 1 to M Join.

Create bridge/Diemsion Tables for the common field between B and C

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions

Appreciate your Kudos.

[PaulDBrown]

@Anonymous 

You need to change the model.

1) delete the many-to-many between B and C

2j create a one-to-many from your date table to each B and C tables.

3) create a new dimension table with unique values for the field you were using in the many-to-many between B and C . Create a one-to-many between this new dimension table and B and C tables.

4) carry on from there