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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
themistoklis
Community Champion
Community Champion

Measures on a separate table with and without a link to a Master table

Hello All,

 

In order to have a tidy data model I thought i should keep calculated fields (measures) on a separate table.

Also this helped me in cases where i had to create measures based on information that was coming from multiple tables.

 

For example, in the attached PowerBI document i have a master table, one table with the TurnOver another table with the Cost.

I want to calculate the Profit using the equation SUM(TurnOver) - SUM(Cost).

As i didnt want to put this measure to any of the existing tables I thought it would be best to keep it separate.

That's why I created a new separate table with the calculated fields.

 

I have found 2 ways to do that but I want to know which is one is the best to use (both in terms of results accuracy and calculation processing). Using both ways i got the same results on Profit.

 

The first method has to do with the creation of a new table using the SELECTCOLUMNS function and then joined it using the taskID to the Master table.

The second method which can also be found here  has to do with the creation of a new Blank table (Home --> Enter Data --> Create Table --> Load) where I have put the new measues. This table is not Joined to any table in the Data Model.

 

My question is which one of the 2 methods described above is most efficient one? One major difference between the methods is that the table from the first method is joined to the data model while the table from the second method is not joined to the data model.

 

Thanks

1 ACCEPTED SOLUTION
v-yulgu-msft
Employee
Employee

Hi @themistoklis,

 

I would prefer method two. From my opinion, there is no need to create a relationship between measure table and master table. Sometimes, redundant relationships might reduce the report performance or affect the result when you make some other calculations.

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-yulgu-msft
Employee
Employee

Hi @themistoklis,

 

I would prefer method two. From my opinion, there is no need to create a relationship between measure table and master table. Sometimes, redundant relationships might reduce the report performance or affect the result when you make some other calculations.

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.