Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hello, currently I have two data tables, the first one represents the revenue based on Project-Role-Yearmonth. This is a sample in Excel.
The second table represents the forecast Based on Project-Yearmonth.
So I want to combine both tables to have something like this. The Role for each project should be filled up, and they are the same for each Yearmonth, the missing revenue in the future month can be blank or 0.
Thank you for reading.
Best Regards,
Minh Pham
Solved! Go to Solution.
Hi @MinhPham96 ,
I'd like to suggest you create a new table with 'CROSSJOIN' function to expand all 'project', 'YearMonth' and 'role' combinations, then you can lookup correspond values from original tables. (I haven't found any detail role mapping from your description)
Table =
ADDCOLUMNS (
CROSSJOIN ( ALL ( T2[Project], T2[YearMonth] ), ALL ( T1[Role] ) ),
"Revenue", LOOKUPVALUE (
T1[Revenue],
T1[Project], [Project],
T1[YearMonth], [YearMonth],
T1[Role], [Role]
),
"Forcast", LOOKUPVALUE ( T2[Forecast], T2[Project], [Project], T2[YearMonth], [YearMonth] )
)
Regards,
Xiaoxin Sheng
Hi @MinhPham96 ,
I'd like to suggest you create a new table with 'CROSSJOIN' function to expand all 'project', 'YearMonth' and 'role' combinations, then you can lookup correspond values from original tables. (I haven't found any detail role mapping from your description)
Table =
ADDCOLUMNS (
CROSSJOIN ( ALL ( T2[Project], T2[YearMonth] ), ALL ( T1[Role] ) ),
"Revenue", LOOKUPVALUE (
T1[Revenue],
T1[Project], [Project],
T1[YearMonth], [YearMonth],
T1[Role], [Role]
),
"Forcast", LOOKUPVALUE ( T2[Forecast], T2[Project], [Project], T2[YearMonth], [YearMonth] )
)
Regards,
Xiaoxin Sheng
Dear @danextian , thank you for replying, but one of my table is generated by using DAX to summarize from other tables, so I cannot use it in the query editor. So I tried to do what you said and use DAX NATURALLEFTOUTERJOIN() but it did not work.
Best Regards,
Minh Pham
In the query editor, create a "unique ID" column by combining Project and Yearmonth in both tables then merge both tables using the new columns to join them.
In DAX, create a calculated column using LOOKUPVALUE(). Do this in your first table assuming that if you combine Project and Yearmonth in your second table, the resulting values will have no duplicates:
Forecast =
LOOKUPVALUE (
Table2[Forecast],
Table2[Project], Table1[Project],
Table2[Yearmonth], Table1[Yearmonth]
)
Proud to be a Super User!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
143 | |
77 | |
63 | |
51 | |
47 |
User | Count |
---|---|
212 | |
82 | |
61 | |
60 | |
57 |