Forum Discussion
Join on date and location within model
This seems like it should be straightforward, but after some serious trawling of previous questions. I haven't found an answer.
I have three tables:
1. Employees
2. Fortnightly sales by employee
3. Sales Targets by region
The employees table is used in the report, but not for this specific question.
I want to plot fortnightly sales by region compared to targets. It would also be great to be able to calculate the difference between the targets and actual sales.
However I can't figure out how to connect tables 2 and 3 on two fields simultaneously.
I want to connect the tables on region and date. However it's only allowing one active relationship. Even when joining on dates alone, this seems problematic as when I create a table with Date (Table 2) and Date (Table 3) one column remains blank.
Any advice would be greatly appreciated.
Your model needs some major rework. A model is suggested to have:
1) several lookup or dimension tables like Date Table, Region-Employee Table in your case, and
2) limited number of record tables, like sales record table and budget table.
3) the dim table shall link with the record table with one-many relationships.
4) as the filter takes place from one side to many side of the relationships, so put the column from the dimension table to row/column/axis/legends of a visual and put the column/measure from the record table mostly to values of a visual.
(In your chart, you have a date column from the many-side of the relationship, so that explains why your chart is not filtering as expected. )
5 Replies
Here's a pbix to demonstrate the issue
https://drive.google.com/drive/folders/1Xm03m4oL_6okWvmCfxbnF-lGJGL1YSGQ?usp=sharing
FreemanZSuper User
Your model needs some major rework. A model is suggested to have:
1) several lookup or dimension tables like Date Table, Region-Employee Table in your case, and
2) limited number of record tables, like sales record table and budget table.
3) the dim table shall link with the record table with one-many relationships.
4) as the filter takes place from one side to many side of the relationships, so put the column from the dimension table to row/column/axis/legends of a visual and put the column/measure from the record table mostly to values of a visual.
(In your chart, you have a date column from the many-side of the relationship, so that explains why your chart is not filtering as expected. )
- FreemanZ
Indeed, there is only one active relationship allowable between two tables. But you could use USERELATIONSHIP to activate a relationship temporally, like this:
NewMeasureWithDate
CALCULATE(
[Measure],
USERELATIONSHIP(Table1[Date], Table2[Date])
)
then the filtering between tables will follow the new temporally activated relationship.
Hi FreemanZ
Thanks for your reply. I'm still not getting it to work as intended.
I now have the active relationship set as the region and I specified the datefields in the USERELATIONSHIP function. But I still can't plot both the targets and sales by community and date.
Hi PBI_User12 ,
Has the problem be solved?
Please consider to mark the reply as solution if it's helpful.
