November 5, 2015 at 3:24 pm #2305
I’m working in the Power BI Desktop client.
I have two data tables, one is a transaction table from our financial system and the other is the line items for the budget. Following the information in the DAX book (chapter 17), I’m trying to create several lookup tables that will filter both tables.
I’ve created a master list of GL numbers and a list of departments. The goal would be to create charts that use data from both tables to compare budget numbers against actual spend AND be able to filter the chart by department and/or GL.
The two bridge tables are comprised of unique line items.
When I connect the two data tables to the first lookup table, it works fine. When I add the second one, I get a dotted line response for the second table and a notice that the tables are already joined. That would be fine, except that I cannot use the second lookup table to filter both tables.
Can anyone give me some guidance?
Thanks.November 5, 2015 at 5:14 pm #2306
The dotted line is an inactive relationship (occurs when there are multiple columns in a single table (“Table A”) that have a relationship with another single table (Table B). If that is the case, something is missing from your model description. Can you take a screen shot of your Power Pivot model in diagram view and attach?November 5, 2015 at 5:39 pm #2307
Attached is the picture.
Attachments:You must be logged in to view attached files.November 5, 2015 at 9:00 pm #2313
A practical and interesting model.
Still thinking, but initial reaction is that you are using two-way filtering (except for departments to budget) between lookup and data tables which I do not think is necessary here. If you set the other relationships also as one-way flowing from lookup to data tables, does that make a difference?
TomNovember 5, 2015 at 9:16 pm #2315
You know, that worked. Those relationships were auto-created by the tool and I hadn’t given the 1-way/2-way setup much thought.
I’m going to keep exploring my data and testing, but that looks like it solved the issue.
Thank you.November 5, 2015 at 9:28 pm #2316
I have also made a note to look at two-way relationships when trouble-shooting the newer data models.
Thanks for the opportunity to learn with you!
Looking forward to working with you again in the powerpivotpro forums.
“May your garden no longer be empty, but rather be filled.”
You must be logged in to reply to this topic.