Trying to Create Column Using Data From Other Tables

[jasonminhas]

My objective is to create a custom column in the Account Table that identifies whether the account has purchased the "New", "Old", "both Old and New", or "Not Old and New" products based on the "Product Tag" column in the Product Table. Below I have created the expected outcome I'm looking for in the Expected Outcome column of the Account Table. How can I create a calculated column in the Account Table to do this?

Here's a link to the .PBIX file: Sample Power BI Model.pbix

Account Table

Account ID	Account Name	Address	Expected Outcome
100	CareerBike	Street DSGS	Old and New
200	BluckBike	Street HJG	Old
300	EverBike	Street TFGD	New
400	Bikey	Street CVBB	Not Old or New

Product Table

Product ID	Product Groups	Cost	Product Tag
Product A	Group 1	120	Old
Product B	Group 1	140	Old
Product C	Group 1	150	Old
Product D	Group 2	170	New
Product E	Group 2	180	New
Product F	Group 2	190	New
Product G	Group 3	125
Product H	Group 4	115

Sales Table

Sales ID	Account ID	Product ID	Quantity	Purchase Date
100	100	Product A	1	03/12/2022
200	200	Product B	1	02/08/2022
300	300	Product H	1	04/02/2022
400	400	Product H	1	04/03/2022
500	100	Product D	1	01/01/2022
600	200	Product G	1	02/15/2022
700	300	Product E	1	03/07/2022
800	400	Product G	1	03/07/2022

[rohit_singh]

Hi @jasonminhas ,

Please try the following :

1)In the report view, ensure you have the following relationships created 

2) Navigate to the sales table and create a calulated column to lookup tag values from the product table

Prod Tag = Related(Model_Product[Product Tag])

3) Create a calculated table from the sales table that has account id, and concatenated values of product tags in a single row. This is to ensure 1 row per account id

Sales Tag =

SUMMARIZE(

Model_Sales,

Model_Sales[Account ID],

"Tags", CONCATENATEX(VALUES(Model_Sales[Prod Tag]), Model_Sales[Prod Tag], ",")

)

4) Create a calculated column on the new table to assign tags as per requirement. 

Tag Desc =

if('Sales Tag'[Tags] = "Old,New", "Old and New",

if('Sales Tag'[Tags] = "Old,", "Old",

if('Sales Tag'[Tags] = ",New", "New",

if('Sales Tag'[Tags] = BLANK(), "Not Old or New",

BLANK()))))

5) Create a relationship between the accounts table and the new calculated table 

6) Finally, add a calculated column to lookup tag values from the new table.

Tag = related('Sales Tag'[Tag Desc])

This should give you the desired output.

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊

[PaulDBrown · Accepted solution]

Try this measure:

Product Tags by Account =
VAR Products =
    CALCULATETABLE (
        VALUES ( sales_table[Product ID] ),
        RELATEDTABLE ( sales_table )
    )
VAR PT =
    INTERSECT ( VALUES ( product_table[Product ID] ), Products )
VAR Tags =
    CALCULATETABLE ( VALUES ( product_table[Product Tag] ), PT )
VAR Ref = { "New", "Old" }
RETURN
    SWITCH (
        TRUE (),
        COUNTROWS ( INTERSECT ( Tags, ref ) ) > 1, CONCATENATEX ( Tags, product_table[Product Tag], " and " ),
        COUNTROWS ( INTERSECT ( Tags, ref ) ) = 1, CONCATENATEX ( Tags, product_table[Product Tag] ),
        "Not Old or New"
    )

The code also works in a calculated column if you prefer

 I've attached the sample PBIX for you