Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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.
Solved! Go to 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.
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
125 | |
78 | |
76 | |
59 | |
51 |
User | Count |
---|---|
166 | |
84 | |
68 | |
67 | |
57 |