Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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
Hi, Don't Desperate. Just change the Cross Filter Direction to Both between Emp and Fact2
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...
Ok, Try with this:
SumColumnWithDax = IF ( HASONEVALUE ( Emp[EmpID] ), CALCULATE ( SUM ( 'Fact 1'[SumColumn] ), FILTER ( 'Fact 1', 'Fact 1'[EmpID] = VALUES ( 'Fact 2'[EmpID] ) ) ), CALCULATE ( SUM ( 'Fact 1'[SumColumn] ) )
Yes, this looks very great! In my small example it works. I'll give it later today a try in my more complex model, but I'm hopeful... I'll come back to post my progress!
But can somebody explain me what happens here; why hasonevalue?
Tried to use it in our own (more complex) model, but we now have problems with multiple facts in 1 fact table.... This gives the situation that the hasonevalue gives FALSE (I think) and makes that he chooses the wrong side of the if
CALCULATE ( SUM ( 'edm uitbetaaldefte'[uitbetaaldefte] ) )
So this is not good...
Can somebody tell us why this HASONEVALUE is in the code? When we understand this better maybe we can arrange a solution....
Another alternative solution more simpler is:
SumColumnWithDax = CALCULATE(SUM('Fact 1'[SumColumn]),RELATEDTABLE('Fact 2'))
Unfortunately. Also not good....
To make it easier I've stripped + anonymised the problem PBIX; it can be found here: https://ufile.io/sih7r
We want to sum 'paid ftes' from table ftes paid, divided by the number of months, per staffmember. We also want to display a property 'relationtype' from table employment.
So still hoping for THE solution
No, staffnumber 111407 has 2 staffids (because of history in our DWH) so when you take staffid in hasonevalue it goes to the 'else' and duplicates for this record.... This happens for a lot of records, so this is not the solution unfortunately...
What happen if you change the Filter Direction Between CostCenter & Employment to Both
And use A simple Calculate(Sum)
That gives:
Table ''costcenter' is configured for row-level security, introducing constraints on how security filters are specified. The setting for Security Filter Behavior on relationship cannot be Both.
Hi, Using your sample file:
Staff & FTE Table have more StaffID than Employment.
My doubt is when you put in a table Staff ID & Employment Relation Type only shows the rows with equals StaffIDs in both Tables.
So if Add of FTE Paids the Sum will be different than the total of FTE.
For Example in your file the value 9,106.60 is the total of FTE (with filters applied) but is not the total if add use the employment table. (2081.66= Only StaffIDs in Employment Table)
This result do you want or 9,106.60.
We want 9106,60 divided by the total number of months, so 9106,60/5 = 1821,32
I send you the file.
https://drive.google.com/file/d/0B95C8CKdTZE3Z3RUYldQekdQVVk/view?usp=sharing
I hope will be helpful to another members in find the solution.
I really appreciate all your work!!! Thank you @Vvelarde!
Hopefully somebody else can think with us and fix it...
I think we'll have to produce one big fact table in the meantime.... But I'm really really really surprised that something like this is so hard in Power BI.
@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?
Proud to be a Super User!
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.
can you show the value for these 3 tables and what they are joining on?
Proud to be a Super User!
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |