DAX: return group from dimension table if there are existing record and return something if not.

[Paulb123]

So I have a fact table like sales data and product dimensional table. They are connected by product id. However, not all records from fact table have product id. I am wondering could I do some sort of DAX measure like following:

IF('fact_table'[product_id] = blank(),
    'product_table'[product_category],

     "something else" --- I would retrieve some text from a field in fact table, but I would like to know if this would work for fact table not containing key to dimension table

Thanks,

[v-kongfanf-msft · Accepted solution]

Hi @Paulb123 ,

You can try formula like below to create calculated column:

ProductCategoryOrText = 
IF(
    ISBLANK('fact_table'[product_id]),
    'fact_table'[description],
    RELATED('product_table'[product_category])
)

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ankit_Rai]

Create a New Column

Enter the following DAX formula

ProductCategoryOrDefault = 
IF(
    ISBLANK('fact_table'[product_id]),
    "No Product ID",
    LOOKUPVALUE(
        'product_table'[product_category], 
        'product_table'[product_id], 
        'fact_table'[product_id], 
        "Category Not Found"
    )
)

Here's what the formula does:

• ISBLANK('fact_table'[product_id]) checks if the product_id in the fact table is blank.
• If the product_id is blank, it returns "No Product ID".
• If the product_id is not blank, it uses LOOKUPVALUE to find the corresponding product_category from the product table.
• If the product_id is not found in the product table, it returns "Category Not Found".

[bhanu_gautam]

@Paulb123 , You can use calculated column using related function 

NewColumn =
IF(
ISBLANK('fact_table'[product_id]),
RELATED('product_table'[product_category]),
"Some other text from fact table"
)