Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
memocyp
Frequent Visitor

Calculated Column based on a condition

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.

Capture.PNG

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

Help is appreciated.

1 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.

View solution in original post

3 REPLIES 3
alanhodgson
Solution Supplier
Solution Supplier

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

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

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.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.