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

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