Help with making powerBI understand Date relationships

[VTB]

hi all, 

I've searched for a while to see if someone solved this, but havent come across it yet. 

Issue i am having is with the limitation of one 1 relationship allowed in powerBI between tables, and date fields not being automatically recognized. 

So the scenario I am trying to solve is:

if I have 2 tables, 

table 1
Ref Date	Drug Trial	Type	Cell Count
12/31/2018	A	yc	50
3/31/2019	B	rt	25
6/30/2019	C	dx	36

table 2
Ref Date	Hold	Trial 1	Result
12/31/2018	6	A	0.26
1/31/2019	7	B	0.95
2/28/2019	3	C	0.27
3/31/2019	9	A	0.40
4/30/2019	4	B	0.17
5/31/2019	12	C	0.91
6/30/2019	70	A	0.11
7/31/2019	30	B	0.67
8/31/2019	3	C	0.55

i am trying to make this work, where i could select Trial & Date from table 1, and it would bring up applicable results from table 2. 

When i make the filter Date & Trial from table 1, say set to A & 12.31, i end up with below

	Table in powerBI
Filter on this:
from table 1	from table 1	from table 1	from table 2	from table 2
Ref Date	Drug Trial	Cell Count	Trial 1	Result
12/31/2018	A	50	A	0.26
12/31/2018	A	50	A	0.40
12/31/2018	A	50	A	0.11

instead of just bring up the first result that matches 12/31, and not bring in the other A results for other dates.

because i have manually set up a relationship between the two tables for powerBI to understand that Drug Trial = Trial 1. 

If i set up Ref Date = Ref Date column, I can control that, but then PowerBI doesnt understand that A in Drug Trial is same A as in Trial 1. 

I've configured both columns to be Dates (from sql perspective, and tables coming into powerbi treat them as such), but it wont allow me to make one of them "Date" (that new powerBI model setting button) because the dates are quarterly, not monthly I think. 

So I can bypass this by simply setting up two Date filters, but thats confusing to end users as to what they need to select. 

I've tried setting up a middle "Date" table to link relationships to that, but that doesnt work either as powerBI doesnt allow more than one reference it seems to a table/column combo. 

Thus... stuck on how to solve this type of issue in powerBI, which comes up quite often when working with multiple tables that share date columns but need other relationships established to filter properly. 😞 

[jdbuchanan71]

Hello @VTB ,

I think you will need a Dates table and a Trails table in order to relate the data the way you want.  You can create the Trials table with this measure.

Trails = 
DISTINCT (
    UNION (
        VALUES(Table1[Drug Trial]),
        VALUES(Table2[Trial 1])
    )
)

This will let you make views relating the values from the tables by date and trial.  Pull in Date from the Dates table and Drug Trial from the Trials table:

[VTB]

Hey, thanks for answering! 

Got as far as creating the Trials and Dates table, union works in the Trials table, but when I go to select columns - dates breaks the powerBI view where it just says "Cant display Visual". 

my tables also dont have the arrows showing the relationships - did you have to define some of them in the "Manage Relationships" for end table to work? 

[jdbuchanan71 · Accepted solution]

Yes, you will need to define the relationships between the tables.  In my example I have the 4.

You want the many side to be Table1 and Table2 and the one side the be Dates and Trials.

Once you have the relationships set the visual should work.

[VTB]

awesome thanks!