Forum Discussion
Creating relationship- Feb 2025 version of PBI
Hi,
I am using a filter in the transform window to filter out a subset of data from Table 1 on ProjName= Blue and Green.
the filter is applied on and only want the relevant information from Table 2 for the filtered values of Table 1 to be displayed. Table 1 and Table 2 are related on the filtered Col ProjName of Table 1 to Approved Projects column of Table 2 .
The expectation was to see a table showing only the Blue and Green projects, along with their priorities, budget allocations, and allocation dates. However I see three extra rows of Red from Table 2 in but corresponding columns form the Table 1 are blank.
A workaround would be to filter out the blanks, but the relationship functionality is broken in my opinion. I did not have to do this filtering out in the past if the filter(transform window) and the relationship was defined properly.
Table 1 -CompanyProjPriority
| ProjName | Priority |
| Blue | A |
| Red | B |
| Green | C |
| Yellow | C |
| Purple | B |
| Orange | C |
Table 2 - ProjectBudget
| Approved Projects | BudgetAllocation | AllocationDate |
| Blue | 40000 | 12/1/2012 |
| Red | 100000 | 12/1/2012 |
| Green | 50000 | 12/1/2012 |
| Blue | 70000 | 2/1/2012 |
| Red | 1000000 | 7/1/2012 |
| Blue | 34000 | 8/1/2012 |
| Red | 1000 | 9/1/2012 |
Result
| Priority | ProjName | Approved Projects | BudgetAllocation | AllocationDate |
| Red | 1000 | 9/1/2012 0:00 | ||
| Red | 100000 | 12/1/2012 0:00 | ||
| Red | 1000000 | 7/1/2012 0:00 | ||
| A | Blue | Blue | 34000 | 8/1/2012 0:00 |
| A | Blue | Blue | 40000 | 12/1/2012 0:00 |
| A | Blue | Blue | 70000 | 2/1/2012 0:00 |
| C | Green | Green | 50000 | 12/1/2012 0:00 |
Super UserThe relationship works, you are just overriding it by including the Approved field from Table 2. Drop that field from your result table (leave ProjName from table1) and you should see what you expect.
Please mark this post as solution if it helps you. Appreciate Kudos.
Hi andrewsommer,
Thanks for the immediate response I dropped it , I still see blanks
Priority ProjName BudgetAllocation AllocationDate 1000 9/1/2012 0:00 100000 12/1/2012 0:00 1000000 7/1/2012 0:00 A Blue 34000 8/1/2012 0:00 A Blue 40000 12/1/2012 0:00 A Blue 70000 2/1/2012 0:00 C Green 50000 12/1/2012 0:00 - andrewsommer
The simplest solution might be on your visual level filters set a filter for Priority that is not blank
I agree that's a workaround, but i don't think in the past versions i have had to filter out blanks, something has changed for sure. Because that's the whole point of creating filters in the transform window and the related relationships in the model view. Sort of works like a SQL inner join not left or right join.
Can you paste your results in here, if it helps i'm on the Feb 2025 version of PBI. Thanks