DAX script to relate two separate fact tables with 1-* relashionship to the dimension table

[sergiorodrigues]

Let's say I have the model below. A Car table, a table with all gas consumed by each car in the company and another table with car status for each date. I know the "Status" column should be modeled in the table with the gas data but it is what it is. 

I'd like to generate a table using DAX with total gas consumed by each car in each status and grouped by month/year like below:

Month/Year	Car	Status1	Status2	Status3
Jan/2020	A	10	13	7
Feb/2020	A	12	9	13
Jan/2020	B	15	7	8
Feb/2020	B	4	70	45

Any suggestions?

[amitchandak]

@sergiorodrigues , You need to have common status and Date table too

how to get a common table in DAX -https://www.youtube.com/watch?v=Bkf35Roman8

power query

https://www.youtube.com/watch?v=kU2M1LmNvNo

https://www.youtube.com/watch?v=vHuhbvYCiNc