Forum Discussion
Problems combining 2 Tables
Hi guys,
I'm having problems combining 2 fact tables and a DAX-measure. Can you please help me to understand what we're doing wrong?
This is our datamodel:
And when we're making a combination of a field in a fact table and a summation of another field from another fact table it's ok:
But when we don't want to sum the field but make a DAX-calculation for it something goes wrong:
We really can't understand this. The DAX-formula is shown in this printscreen; as you can see it sums the uitbetaaldefte field, just like our first printscreen, but it divides it by something from the date dim. But this gives a strang multiplication of data; the person that had 1 'arbeidsrelatie' in first printscreen now has all the available 'arbeidsrelatie' choices plotted to it; this is not correct...
We think it happens because of combining 2 fact tables, but we also do this in printscreen 1 and there it gives no problem, so it is caused by our DAX-calculation. But even when we change the DAX to this the multiplication comes:
Aantalftes_Uitbetaald =
SUM ('edm uitbetaaldefte'[uitbetaaldefte])But this DAX must be the same as the normal SUM.....
So we are really desperate... What are we understanding/doing wrong?
Our datamodel is:
19 Replies
MiKeZZa there is a lot of info here but i dont quite understand what you saying. is it possible to say what you are expecting? is it because the figure is duplicating? its hard to understand because the measure language isn't english is the table1 a calculated table?
loosk like it could be a relationship issue though, maybe a mapping missing or something that needs to be bidirectional vs single?
We're expecting that we get the name of the person with 03: Onbepaalde tijd and as outcome of the measure 0.89. So indeed no duplicating... Now, when we use the DAX-measure, we don't only get 03: Onbepaalde tijd, but also other options....
The measure must sum the same field as we use in picture 1 and divides it by a distinct count... So it must be possible in our opinion....
Table 1 is a handmate table without relationships, it's only to host the dax things, so that they are not deleted when I delete a table in future.
I've reproduced the problem in an almost empty file with dummydata. I think it will be clear what the problem is, but for sure: the first matrix is correct, the second one gives duplication.
In my opinion it's the same situation, but the second is with DAX. And it's not the same I see in output...
File is here; Click here
Yes, that's great! That works really good for my simplified example, good to know!!!
But in our model there is also a both cross filter on costcenter, because of that the security is done by this tables. See printscreen 3 of openingpost. So I can't make this, because of this message:
So I'm still (a little) desperate...
