cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Community Champion
Community Champion

Re: Problems combining 2 Tables

@MiKeZZa

 

Another alternative solution more simpler is:

 

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




Lima - Peru
Highlighted
Post Patron
Post Patron

Re: Problems combining 2 Tables

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 Smiley Frustrated

Highlighted
Community Champion
Community Champion

Re: Problems combining 2 Tables

@MiKeZZa

 

With a little modification

 

Modi.png




Lima - Peru
Highlighted
Post Patron
Post Patron

Re: Problems combining 2 Tables

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

Highlighted
Community Champion
Community Champion

Re: Problems combining 2 Tables

@MiKeZZa

 

 

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

 

And use A simple Calculate(Sum)

 




Lima - Peru
Highlighted
Post Patron
Post Patron

Re: Problems combining 2 Tables

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.

Highlighted
Community Champion
Community Champion

Re: Problems combining 2 Tables

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

 

 

 




Lima - Peru
Highlighted
Post Patron
Post Patron

Re: Problems combining 2 Tables

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

Highlighted
Community Champion
Community Champion

Re: Problems combining 2 Tables

@MiKeZZa

 

I send you the file.

 

https://drive.google.com/file/d/0B95C8CKdTZE3Z3RUYldQekdQVVk/view?usp=sharing

 

I hope will be helpful to another members in find the solution.

 

 




Lima - Peru
Highlighted
Post Patron
Post Patron

Re: Problems combining 2 Tables

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.

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors