cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Super User I
Super User I

Re: Problems combining 2 Tables

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Post Patron
Post Patron

Re: Problems combining 2 Tables

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.

Highlighted
Super User I
Super User I

Re: Problems combining 2 Tables

@MiKeZZa

 

Capture.PNG

 

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Post Patron
Post Patron

Re: Problems combining 2 Tables

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

Highlighted
Community Champion
Community Champion

Re: Problems combining 2 Tables

@MiKeZZa

 

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

 

Change.png

 

Sustent.png

 




Lima - Peru
Highlighted
Post Patron
Post Patron

Re: Problems combining 2 Tables

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

Highlighted
Community Champion
Community Champion

Re: Problems combining 2 Tables

@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
Highlighted
Post Patron
Post Patron

Re: Problems combining 2 Tables

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?

Highlighted
Post Patron
Post Patron

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

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors