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

 

Another alternative solution more simpler is:

 

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

MiKeZZa's avatar
MiKeZZa
Post Patron
9 years ago

Unfortunately. Also not good....

 

To make it easier I've stripped + anonymised the problem PBIX; it can be found here: https://ufile.io/sih7r

 

We want to sum 'paid ftes' from table ftes paid, divided by the number of months, per staffmember. We also want to display a property 'relationtype' from table employment.

 

 
Combining these 2 facts gives problems.
 
In PBIX there are 3 tables at the bottom; the first has a sum on the table. Everything is ok, except that we want to divide by the number of months. There we need DAX. In table 2 and 3 are the DAX things we've tried with help of Vvelarde. But both are not good; in the most right one we mis many record.
 
In the middle one we with some selections made have a 'cant display visual' error, so DAX seems to be not completely good... And:
  1. We have to display staffnumber in the visual, otherwise things go wrong
  2. When we have more than 1 record in 'employment' for an employee in 1 month things go wrong.

 

So still hoping for THE solution :smileyfrustrated:

  • Vvelarde's avatar
    Vvelarde
    Community Champion
    9 years ago

    MiKeZZa

     

    With a little modification

     

  • MiKeZZa's avatar
    MiKeZZa
    Post Patron
    9 years ago

    No,  staffnumber 111407 has 2 staffids (because of history in our DWH) so when you take staffid in hasonevalue it goes to the 'else' and duplicates for this record.... This happens for a lot of records, so this is not the solution unfortunately...

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