Calculated Column based on a condition

[memocyp]

Hi,

I have the following table where values are coming from different tables. It's a star schema where I have Sales Facts, Promo Dimension, Product Dimension.

What i need is a column FLAG to identify PROMOTION_IDs which contains "BEVERAGES" and "FOOD".

Help is appreciated.

[alanhodgson]

Hey @memocyp,

You can use this DAX statement to create the calculated column:

= IF(OR('Table'[Prod_Hier4_Name] = "Food", 'Table'[Prod_Hier4_Name] = "Beverages"), "True", "False")

If the Promotion_ID contains "Food" or "Beverages" then the column will say "True".

Hope this helps,

Alan

[memocyp]

Hi @alanhodgson,

Thanks for the suggestion, but it won't work.

When I try to create calculated column in the Promo Dimension table, it will only allow me to use columns from this table, not others.

As I mentioned in original post, 3 fields of this table come from different data sources. Promotion_ID comes from Promo Dimension, Product_HIER4_Name comes from Product Dimension and UNITS is coming from Facts Sales. Item_Number from Fact Sales is related to Product Dimension and Promotion_ID is related to Promo Dimension as primary keys.

I am guessing solution might be something that uses "RELATED" but couldn't figure out.

Best,

Memo

[jmalone · Accepted solution]

There may be some confusion between your description of the schema/model and the attached screenshot. The picture makes it appear like your three columns are in a single table. However, after reading your description, you should be able to create a calculated column using the IF() and RELATED() functions.

To help understand RELATED(), you can simply add a calculated column in the Promo Dimension table:

Related Test Column = RELATED('Product Dimension'[Product_HIER4_NAME])

If your model is set up correctly (with a one-to-many relationship from Product Dimension to Promo Dimension), you should see the Product_HIER4_Name values in this new column. If it doesn't work, you might need to check the relationship between these two tables.

Once you've gotten the RELATED() function to work, you can use it in an IF() conditional statement. Also in Promo Dimension table.

Something like:

New Calculated Column = IF( RELATED('Product Dimension'[Product_HIER4_NAME]) = "Food" ) , TRUE() , FALSE() )

This should return True for Food, and False for everything else.

Just add the OR() function as mentioned earlier to use additional conditions.