Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
cfosund
Helper I
Helper I

how to work with two fact tables?

Hi, 


I need some help to understand how I should work with two fact tables that does not have all the dimensions in each fact table. 

I have uploaded an example pbix so you can easily try it out.

 

Link to pbix:

https://ingraphic-my.sharepoint.com/:u:/g/personal/christian_fosund_ingraphic_no/EVhFayxBJoZHqa7Mu9G...

cfosund_0-1612944725179.png

 

I want to calculate the total salery amount based on days worked and the daily salery. 

The model should also allow me to filter by location, salery type and month.

 

The issue I meet is that I don't have the location in the days worked table. I only know how many days they have worked. So when create a table it will not filter out Lisa who has not worked at Building 1. What is the best way of solving this?

 

Is it possible to create a measure that allows me to calculate the totalt salery correctly or maybe create one fact table with the days worked, and still be able to filter by location and salery type?

 

cfosund_1-1612944069099.png

 

the wanted output in the example shold be: 

Namedaily salerydays workedSallery total

Christian1700,952339121,85
Scot1882,452750826,15
Total3583,450179170

 

 
1 ACCEPTED SOLUTION

In additions I implemented the solution for you. Below the calculated table (first disconnect Days and then calculate table!)

extraTable = 
VAR __cartesian = ALL(Salery[EMP Number],Salery[Month], Salery[Location])
VAR __tableWithAllocatedDays = 
ADDCOLUMNS(
    __cartesian,
    "@AllocatedDays",
    DIVIDE(
        SUMX(FILTER(Salery, Salery[EMP Number] = EARLIER(Salery[EMP Number]) && Salery[Month] = EARLIER(Salery[Month]) && Salery[Location] = EARLIER(Salery[Location])), Salery[Daily salery]),
        SUMX(FILTER(Salery, Salery[EMP Number] = EARLIER(Salery[EMP Number]) && Salery[Month] = EARLIER(Salery[Month])), Salery[Daily salery])
    ) *
    SUMX(FILTER('Days at work', 'Days at work'[Emp Number] = EARLIER(Salery[EMP Number]) && 'Days at work'[Month] = EARLIER(Salery[Month])),'Days at work'[Days])
)


RETURN
__tableWithAllocatedDays

Giving this visual:
Picture1.png

View solution in original post

3 REPLIES 3
JW_van_Holst
Resolver IV
Resolver IV

Allocate the Days to a location on basis of the weighted average of Daily Salary per Location. So EMP number 1 will have the Days allocated for 99.9% to location 1 and for 0.1% to location 2.
Make a new table with these allocations, disconnect the old Days table and connect the new one to the dimension tables (including Locations).

 

Good luck!

In additions I implemented the solution for you. Below the calculated table (first disconnect Days and then calculate table!)

extraTable = 
VAR __cartesian = ALL(Salery[EMP Number],Salery[Month], Salery[Location])
VAR __tableWithAllocatedDays = 
ADDCOLUMNS(
    __cartesian,
    "@AllocatedDays",
    DIVIDE(
        SUMX(FILTER(Salery, Salery[EMP Number] = EARLIER(Salery[EMP Number]) && Salery[Month] = EARLIER(Salery[Month]) && Salery[Location] = EARLIER(Salery[Location])), Salery[Daily salery]),
        SUMX(FILTER(Salery, Salery[EMP Number] = EARLIER(Salery[EMP Number]) && Salery[Month] = EARLIER(Salery[Month])), Salery[Daily salery])
    ) *
    SUMX(FILTER('Days at work', 'Days at work'[Emp Number] = EARLIER(Salery[EMP Number]) && 'Days at work'[Month] = EARLIER(Salery[Month])),'Days at work'[Days])
)


RETURN
__tableWithAllocatedDays

Giving this visual:
Picture1.png

Thanks you so much! You saved my week and I learned alot 🤩

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.