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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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