Forum Discussion

MiKeZZa's avatar
MiKeZZa
Post Patron
9 years ago

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 combinat...
Need Help
MiKeZZa's avatar
MiKeZZa
Post Patron
9 years ago

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

Vvelarde's avatar
Vvelarde
Community Champion
9 years ago

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

 

 

  • MiKeZZa's avatar
    MiKeZZa
    Post Patron
    9 years ago

    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?

    • MiKeZZa's avatar
      MiKeZZa
      Post Patron
      9 years ago

      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's avatar
        Vvelarde
        Community Champion
        9 years ago

        MiKeZZa

         

        Another alternative solution more simpler is:

         

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