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

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

MiKeZZa

 

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

 

 

 

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