Forum Discussion
Problems combining 2 Tables
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.
Combining these 2 facts gives problems.In PBIX there are 3 tables at the bottom; the first has a sum on the table. Everything is ok, except that we want to divide by the number of months. There we need DAX. In table 2 and 3 are the DAX things we've tried with help of Vvelarde. But both are not good; in the most right one we mis many record.In the middle one we with some selections made have a 'cant display visual' error, so DAX seems to be not completely good... And:- We have to display staffnumber in the visual, otherwise things go wrong
- When we have more than 1 record in 'employment' for an employee in 1 month things go wrong.
So still hoping for THE solution :smileyfrustrated:
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...
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.