Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
650AA
Helper I
Helper I

Power 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

650AA_0-1710517272384.png

 

I have also attached a picture of the model  --Any help or advice is much appreciated 

650AA_1-1710517330431.png

 

 

5 REPLIES 5
MNedix
Solution Supplier
Solution Supplier

Create the realtionship and let it inactive in the model. Use USERELATIONSHIP function when creating the measure.

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 ?

650AA_0-1710709525685.png

 

MNedix
Solution Supplier
Solution Supplier

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

samratpbi
Solution Supplier
Solution Supplier

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. 

Hi 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 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.