DAX query is producing incorrect data in a visual - what am I doing wrong?

[Anonymous]

Hi,

I've been asked to help complete a task in a report. Having just taken hold of the .pbix file I created a couple of DAX queries hoping it would complete the task, it didn't. What I found is the DAX queries caused, what looks like, a crossjoin affect.

This task is required to be complete asap so I'm reaching out to you to help please. I will continue working on it in the meantime too.

Rather than explain what I think is happening I will just explain the set-up and hope you can shed some light.

The starting table visual looks like this:

Each WBS is unique in the screenshot above is unique (I've blackened it out for data sensitivity reasons). WBS (WBS table), Amount GC (WBS table), Commitment (WBS Commintment table), Forecast (WBS Forecast table), and Budget (WBS Budget table).

The relationship view of the above tables is captured below:

The simple two requirements are as follows:
A measure add 'Amount GC' and 'Commitment'  (Total = SUM(WBS[Amount GC]) + SUM('WBS Commitments'[Commitment]))

A measure to subtract the 'Total' measure from Budget  (Total to Budget = SUM('WBS Budget'[Budget]) - tblMeasures[Total])

When I create them using the DAX above and place them into the table visual the following happens:

1) When placing the 'Total' measure into the table:-

Notice how for WBS 1407 and 1415 the values seems to have been repeated.

2) When placing 'Total to Budget' measure in the table (with the 'Total' measure already present):-

Notice how 'Budget' and 'Forecast' are duplicated.

Please can someone point me in the right direction?

Thanks in advance.

[Anonymous]

You could start by removing all your cross-direction filtering in the model and see if you are getting the numbers you expect. Then work your way back by changing it back one-by-one, and see which one of those is causing the problem.

[Anonymous]

Thanks for the response.

I've actually tried that already, however for each bi-directional join when I attempt to change it to a single direction I receive 'The filter direction you selected isn't valid for this relationship' message.

The DAX measures in the post above are standard DAX measures so why are they having the affect on the table visual's data?

If you can suggest anything further do let me know?  I will disect how the report has been put together and hopefully this show some light.

[vcastello]

Hi @Anonymous

Which table and column are you using in the visual to obtain the WBS Column?

Is it the one from Project Owners? If not ... could you try ?

[Anonymous]

Hi @vcastello. Thanks for the reply.

The WBS column in the visual was coming from the WBS table. I removed it and placed the WBS column from the Project Owners table. Below is the screenshot of the results. To help highlight the problem I have used the Project Owner filter of 'Premises'.

This is the result without the two DAX measures:

This is the result with the two DAX measures included.

The only difference between the two results is the inclusion of the two DAX measures in the second screenshot.

You can see in the second result all the additional lines. The only line I am expecting to be returned is the one with the red pointing arrow (the same line as in the first result but with the inclusion of the two DAX measures).

Any ideas?

[vcastello]

Hi @Anonymous

Maybe it's a granularity problem. Several tables have differnt 'unit measures' for the same concepts.

Maybe this can help you

http://www.daxpatterns.com/budget-patterns/

Hope That Helps

Vicente

[Anonymous]

Thanks @vcastello

I am struggling here still.

Let's break it down. I want to add the Amount GC value (table WBS) to the Commitment value (table WBS Commitment)  WHERE  WBS (table WBS) matches WBS (table WBS Commitment)

The join is a table WBS (field G/L Account) *:1 against the table GL Link (field G/L Account) 1:* against table WBS Commitments (field G/L Account).

Considering the granularity, how would I write this using DAX?

I am attempting this DAX formula myself but I'm getting in a right pickle. I'm using combinations of SUMMARIZE, SUM, SUMX, CALCULATE, RELATED...

[vcastello]

hI @Anonymous

Is there any way toi access your data model.? Maybe a small sample. That would help.

Vicente

[Anonymous]

@vcastello, For this data model I would classify all the data as sensitive so I'm afriad I cannot share it. It doesn't help, I know.

I'm trying to write a simple DAX formula. Something like, Total = SUMX( 'WBS', 'WBS'[Amount GC] + RELATED( 'WBS Commitment'[Commitment] ) )  however the 'WBS Commitment' table is being recongnised. Maybe because it is not a directly related table. The join would be WBS to GL Link to WBS Commitment (as per my previous post's example). I thought maybe DAX would work out the linking join and I not need to explicity use it in the DAX formula. I guess not.

Are you able to write a generic formula for my previous post's requirement? From that I should be able to pick, if needed, to get it right. To not be lazy, I will continue with the understanding of DAX and granularity.

Thank you.

[Anonymous]

I'm getting closer.

The measure used:
Total = SUMX( WBS, WBS[Amount GC] +
CALCULATE( VALUES( 'WBS Commitment Summary'[Commitment] ),
FILTER( 'WBS Commitment Summary',
'WBS Commitment Summary'[WBS] = WBS[WBS])))

Produces this result:

So you can see the cross join affect is no longer present, however the totaling is not correctly for some of the values.

For example, the WBS value of 407 and 418 are summing incorrectly. The WBS value of 1402 is summing correctly. The total column should be the sum of Amount GC and Commitment.

And yes, I know I am now referencing the WBS Commitment Summary table and not the WBS Commitment table to find the Commitment value. So my measure formula in previous posts is not longer valid.

[Anonymous · Accepted solution]

Guys,

I'm placing a post here to close this off so not to leave it open. I actually resolved this 2 weeks back.

In short, I did the following:
1) Removed all the inherited table joins.

2) Created only the joins required to satisfy the requirement.

3) Created two simple DAX measures to satisfy the requirement.

4) Amend the formatting of the measures in the visual to complement the DAX formulas used.

Very simple in the end. I simpy had to break it down and remove a lot of what I inherited.