Forum Discussion
Helper IPower BI Relationships
Hi all am tried to do some data modelling where my enhancement finance table has no dates assigned to it, as such I have split the table into multiple tables to assign each column a fiscal year eg Column name - Current Yr 1 = 2024, Column name - Current Yr 2 = 2025, and so on.
As I only need to consider the fiscal Year , I have created a DateTableTest with one column in that runs from 2024 to 2030 and created a relationship between the seperated tabled. However I wish to use the enhancement finance table as a reference table for all the other table as I have to consider programe schemes region etc
When I do a distinct count it sums the programes 6 times rather then just counting it from the enhanecemnt finance table once as I am unable to build an active relationship and get the following error for all the tables except for BFRCCP1
I have also attached a picture of the model --Any help or advice is much appreciated
5 Replies
- samratpbi
Super User
Not sure about the data and which one is Fact and Dimension table, however best practice is, try to have Join Cross Filter Direction as Single (instead of Both) and try to have 1 to many joins. Looking into the error, it seems your joins creating ambiguity.
650AAHi the Fiance Table had to split into 5 spererate tables as there were no dates assigned for each column, therefore I split the table and added a date column to each table as each table represents a different year. However the main Fianance Table has information relation to region route etc and I wanted to created a relation ship with the 5 tables and the fianance table , however I am unable to do
- MNedix
Solution Sage
Create the realtionship and let it inactive in the model. Use USERELATIONSHIP function when creating the measure.
- 650AA
Hi I have slightly change the model what I am trying to achieve is the top 15 programme sales across all 6 tables, so how would I apply the USERRELATIONSHIP - would I need to create measures on all the tables ?
- MNedix
You will need to use it whenever you need it. Given your example, you will use it when creating a measure which uses a column in BFRCCPS and another one in DateTableTest, because that relationship is inactive. You won't have to use it for a measure between enhancement finance table and DateTableTest (since that relationship is active).
PS: as best practice, you should avoid as much as possible setting the Cross-filter direction to Both
