Create measure that selects value from multiple tables to display in matrix

[lilych]

Hi -

I am trying to create a matrix that shows all accounts against all products, and either the live date or close date within the matrix. This data comes from two different entities within the CRM system. 

I have brought in the desired entities to Power BI and have the fields I think I need - Entity, Account, Product, Live or Close Date - in two tables. The goal is: if there is an account / product combination within Entity1, display that live date in the matrix. If not, display the close date from Entity2. There may be some cases where the account / product combination exists in both entities, in which case, i need it to show the live date from Entity1.  If the account/product combination doesnt exist in Entity1, and it does in Entity2, i need it to display the close date from Entity2. I would then use conditional formatting to display all live dates in green and close dates in another color.

Entity1

accountid	productid	livedate
Account1	ProductA	5/10/2018
Account1	ProductB	1/31/2017
Account2	ProductB	10/04/2016

Entity2

accountid	productid	closedate
Account1	ProductB	9/31/2021
Account2	ProductA	11/29/2020

Desired Matrix: 

	ProductA	ProductB
Account1	5/10/2018 (From Entity1)	1/31/2017  (From Entity1)
Account2	11/29/2020 (From Entity2)	10/4/2016 (From Entity1)

For account1-productB, the date pulled in is from Entity1 vs. Entity2.

What is the best way to create a measure that can accomplish this? Would i need to append the two tables or is that not necessary?

Thank you for the help.

[Greg_Deckler]

Sample data would be great. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[lilych]

Edited my question with sample data. Thank you!

[v-juanli-msft · Accepted solution]

Hi @lilych 

Add merged column for both tables in edit queries,

Create a new table

dim table =
ADDCOLUMNS (
    CROSSJOIN (
        DISTINCT (
            UNION (
                VALUES ( entity1[accountid] ),
                VALUES ( entity2[accountid] )
            )
        ),
        DISTINCT (
            UNION (
                VALUES ( entity1[productid] ),
                VALUES ( entity2[productid] )
            )
        )
    ),
    "combination_id3", [accountid] & "_" & [productid]
)

Create measures

new date = IF(MAX(entity1[livedate])=BLANK(),MAX(entity2[closedate]),MAX(entity1[livedate]))

color rule = IF(MAX(entity1[livedate])=BLANK(),1,2)

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.