Forum Discussion

lilych's avatar
lilych
Icon for Helper II rankHelper II
6 years ago
Solved

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

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

accountidproductidlivedate
Account1ProductA5/10/2018
Account1ProductB1/31/2017
Account2ProductB10/04/2016

 

Entity2

accountidproductidclosedate
Account1ProductB9/31/2021
Account2ProductA11/29/2020

 

Desired Matrix: 

 ProductAProductB
Account15/10/2018 (From Entity1)1/31/2017  (From Entity1)
Account211/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.

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    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.

3 Replies