Add Column Based on Other Table Value

DAX DAX Calculations
1

I have two tables, Subscriptions and Products.
The SUBSCRIPTIONS TABLE has a PRODUCT ID field. The Product_IDs Table has a PRODUCT ID, PRODUCT NAME Field and a REVENUE CATEGORY field. The relationship is many PRODUCT ID in the SUBSCRIPTIONS table to one PRODUCT ID in the Products table. I tried to add a column using the custom column functionality but can’t get it to find the REVENUE CATEGORY column in the Products table. How can I get a new column in the Subscriptions Table to show the REVENUE CATEGORY from the Products table? Thank you for any assistance you can offer with this.

2

Hi @ScottTPA,

I’m wondering why you would want that show revenue category in Subscription table. if i’m reading this correctly, you already have it linked up to revenue category through product id within subscription table and Products table .

Product Id (Subscriptions table) is linked in Products Table on Product id item.

Products Table (Product id item) referencing to columns product name and revenue category with in the Product table

You should be able to create report on revenue category

I think i’m reading this correctly.

thanks Ketih

3

Thank you for your reply. It gets more complicated then I Illustrated here but for simplicity I narrowed it down to just this set of facts. The reason I want to add this column is there is a third table that I need to used to summarize some information by the revenue category. That table is related via a different field that is not available in the product ID table.

4

not a problem. I’ve just been taught having the same information in different tables will get difficult to keep them updated properly

sorry

5

@ScottTPA,

If I understand the structure properly, try creating a calculated column in the subscriptions table:

Column = RELATED( ‘Products’[REVENUE CATEGORY] )

  • Brian
6

Hi @ScottTPA,

The Power Query equivalent is to go to the Subscriptions table and Merge it with the Product table on Product ID, then only expanding the Revenue category.

However I agree with @Keith it’s not desirable to have an attribute in more than one table. So if you can remove it again before load after you’ve finished with your other task that would be preferred. In case that somehow isn’t possible, make sure to hide the column in report view - so it can’t be used by mistake.

I hope this is helpful.

1 Like