Modelling Issues

Hi there!

I’m merging two sets of data: Forecast (live_data) and Actual (SAP Actuals).

When building a matrix, I’m using a field which is common across the two, which is the ProjectKey (noticed this field is called “Applicant” in SAP Actuals).

Problem is that if I use only Forecast code (coming from live_data) I get the right ProjectKey by Company:

image

However, if I drag Spend (coming from SAP Actuals) I have ProjectKey from other Companys that doesn’t belong to India for this case. Actually, I get all ProjectKey available for all CapEx Company which is wrong, meaning I should only get the right ProjectKey by Company (see C-US-…(this is for United Stated and is popping up under India)

I’ve re-visited my model several times and not sure what might be causing this issue…

Any hint here would be highly appreciated

Big thanks

CAPITAL EXPENSES.pbix (716.5 KB)

HI @SamSPAIN

The reason is project key is coming from live data and it is not filtering sap actuals but since you have dragged spend from sap actuals it is doing a crossjoin between sap actuals and live data and hence you are seeing all the capex company. You should have project key coming from project lookup table like companies .

Lemme know if this helps.

regards,
Hemant

Hi @SamSPAIN,

As highlighted by @Hemantsingh, ProjectKey is from Live Data and it is on the many side of relationship with Companies table, so it will not filter Companies and hence Companies will not be able to apply filter to Sap actuals. One way to understand is that if you apply bidirectional filtering between Companies and Live Data, then Live Data will be able to fileter Companies and then it will work, but it can create other issues for other measures. So, it’s better to solve it by creating a separate lookup table. I tried to use CROSS FILTER function within Calculate, but it did not work.

image

I tried to do it within Measure, but it did not work out.
Spend =

VAR vspend = 
DIVIDE(
    SUM( 'SAP Actuals'[Val.in rep.cur.] ),
    1000
)
RETURN
CALCULATE(vspend,CROSSFILTER('live_data'[CapEx Company],'Companies'[CapEx Company],Both))

Kind Regards,
Hafiz

hi both thanks for your feedback - I managed to sort this out.

It didn’t workout because you can’t change the value of a variable in DAX, in DAX a variable is actually a constant, so the CROSSFILTER modifier of CALCULATE has no impact on vspend, as vspend has already been calculated.

1 Like

Awesome @AntrikshSharma . You are right and I was making this mistake. After wrapping measure inside Calculate, it is working as expected without enabling bidirectional filter.

@SamSPAIN great to hear that you sorted out your issue.

Final solution which works for me is below:

Spend = 
CALCULATE(
DIVIDE(
    SUM( 'SAP Actuals'[Val.in rep.cur.] ),
    1000
),CROSSFILTER('live_data'[CapEx Company],'Companies'[CapEx Company],Both)) 

1 Like

Hi @SamSPAIN, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!