Forum Discussion
How to make relation between two tables with two fields
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 :
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.
3 Replies
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
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.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.
