Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MiKeZZa
Post Patron
Post Patron

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:

 

problem_1.png

 

But when we don't want to sum the field but make a DAX-calculation for it something goes wrong:

problem_2.png

 

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:

problem_3.png

19 REPLIES 19
MiKeZZa
Post Patron
Post Patron

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

Vvelarde
Community Champion
Community Champion

@MiKeZZa

 

Hi, Don't Desperate. Just change the Cross Filter Direction to Both between Emp and Fact2

 

Change.png

 

Sustent.png

 




Lima - Peru

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:

 

problem_4.png

 

So I'm still (a little) desperate...

Vvelarde
Community Champion
Community Champion

@MiKeZZa

 

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] ) )

 

Sustent.png 




Lima - Peru

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....

Vvelarde
Community Champion
Community Champion

@MiKeZZa

 

Another alternative solution more simpler is:

 

SumColumnWithDax = CALCULATE(SUM('Fact 1'[SumColumn]),RELATEDTABLE('Fact 2'))




Lima - Peru

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:
  1. We have to display staffnumber in the visual, otherwise things go wrong
  2. When we have more than 1 record in 'employment' for an employee in 1 month things go wrong.

 

So still hoping for THE solution Smiley Frustrated

Vvelarde
Community Champion
Community Champion

@MiKeZZa

 

With a little modification

 

Modi.png




Lima - Peru

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...

Vvelarde
Community Champion
Community Champion

@MiKeZZa

 

 

What happen if you change the Filter Direction Between CostCenter & Employment to Both

 

And use A simple Calculate(Sum)

 




Lima - Peru

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.

Vvelarde
Community Champion
Community Champion

@MiKeZZa

 

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.

 

 

 




Lima - Peru

We want 9106,60 divided by the total number of months, so 9106,60/5 = 1821,32

Vvelarde
Community Champion
Community Champion

@MiKeZZa

 

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.

 

 




Lima - Peru

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.

vanessafvg
Super User
Super User

@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?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

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.

@MiKeZZa

 

Capture.PNG

 

 

can you show the value for these 3 tables and what they are joining on?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.