cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Missred
Regular Visitor

DAX and table structure/relationships advice

Hi

 

I'm building a portfolio reporting tool which monitors financial spend on projects. Part of my data model includes the three tables shown and described below:

 

Project details - master data of project details i.e. ID, name, finance system codes. Each row represents a single project.

Summary - high level finance data for each project split by FY and funding source.

Budget - budget for each project split by FY and funding source.

 

Current active relationships:

Summary to Project details based on Project ID (Single, Many-to-1)

Budget to Project details based on Project ID (Single, Many-to-1)


Model.png

 

I want to create a column in the summary table which calculates the project's budget variance = Budget - (Actual+Forecast). Actual+Forecast is in the summary table, but Budget is in the budget table. [NOTE: you will see columns called variance already in the summary table but these are based on an approval value not the budget value). 

 

I tried creating various measures and columns, but I think because the relationship both tables have to project details is only based on project ID the numbers are incorrect i.e. I don't just need the project's total budget, I need the budget for each specific FY and funding source.  

 

I also tried creating a unique budget ID in the summary and budget tables as a string concatenation of project ID + funding source + FY and then creating a relationship between the two tables, but Power BI wouldn't allow the relationship to be active as it would create ambiguity.

 

Am I able to achieve what I need with DAX formulas? Or am I forced to need to restructure the project details table?

 

Thanks in advance and sorry for the long post!

Rachel

1 REPLY 1
v-cherch-msft
Microsoft
Microsoft

Hi @Missred

 

It seems you may use USERELATIONSHIP Function.You may refer to below articles.

https://www.sqlbi.com/articles/userelationship-in-calculated-columns/

http://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in-...

 

Regards,

Cherie

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

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors