Forum Discussion

memocyp's avatar
memocyp
Frequent Visitor
9 years ago
Solved

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. What i need is a column FLAG ...
  • jmalone's avatar
    jmalone
    9 years ago

    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.

alanhodgson's avatar
alanhodgson
Solution Supplier
9 years ago

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's avatar
memocyp
Frequent Visitor
9 years ago

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's avatar
    jmalone
    Resolver III
    9 years ago

    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.