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

 

 

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

 

And use A simple Calculate(Sum)

 

MiKeZZa's avatar
MiKeZZa
Post Patron
9 years ago

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

    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.

     

     

     

  • MiKeZZa's avatar
    MiKeZZa
    Post Patron
    9 years ago

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

  • MiKeZZa's avatar
    MiKeZZa
    Post Patron
    9 years ago

    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.