addcolumns from two dimensions tables

[mim]

i Have two fact tables joined to two dimensions tables, i am trying a dax query to return a calculated table, this dax works for one column, but i don't how to extended to the second column

EVALUATE
addcolumns(values(tag[tag]),"earned hours",mstdates[earned hour],"budget hours",mstdates[budget hours])

i want to add a new column area[area]

thanks for your help

edit : i was missing the use of cross join to related different dimensions 

thanks for this blog post

[richbenmintz]

What if you create a couple of measures, to represent your facts and write something like:

evaluate(
summarize
(
'mstdates'
,'tag'[tag]

,'area'[area]
,"earned hours", [earned hour]
,"budget hours", [budget hours]
)
)

[mim]

summarize will work only for 1 table, at least that's my understanding, that's why, i am using addcolumns ?

[v-ljerr-msft · Accepted solution]

Hi @mim,

Is it possible for you to merge the two fact tables together into one table first in Query Editor? Then you should be able to use ADDCOLUMNS or SUMMARIZE Function (DAX) to get the result you need in this scenario.

Reference: 

POWER BI DESKTOP : MERGE QUERY OPTIONS

Best Practices Using SUMMARIZE and ADDCOLUMNS

Regards

[richbenmintz]

summarize will work for on base table, but you are able to reference columns in related tables and use measures

[Anonymous]

Hi,

Could you post a schema or describe how the relationships look for your tables: tag, mstdates and area?

Since Addcolumns will evaluate all expressions on each row for your "base" table, and the expression must evaluate to a scalar value the relationship from table tag to the table area should be many to one.

Does this make sense?

Br,

Magnus 

[mim]

I have two fact tables

fact table 1(tag, area, earned hours)

fact table 2(tag, area, budget hours)

the tags and area are not always the same in the two tables 

two Master tables

tag(tag) (PK)

area(area)(PK)

i am looking to have this result 

tag,area, sum(earned hours), sum(budget hours)

msdate is just a dummy table for storing measures.