IF condition across multiple tables returns blank/null values

To achieve your desired output in Power BI using DAX, you can create a calculated column in Table2 (Transaction table) that incorporates the logic you mentioned. Since you want to display values in Table2 based on conditions involving both Table2 and Table1 (Calendar table), you can use functions like RELATED and FILTER. Here's a step-by-step guide on how to do this:

1. Open Power BI Desktop and go to the "Model" view.

2. Create a calculated column in Table2 (Transaction table) by following these steps:

   • In the "Fields" pane, select "Table2."
   • Click on the "Modeling" tab in the ribbon.
   • Click on "New Column."

3. In the formula bar, enter the following DAX formula:

Symbol =
IF (
Table2[weekday_type] = "Weekend",
"symbol1",
IF (
Table2[weekday_type] = "weekday" && ISBLANK ( RELATED ( Calendar[ref date] ) ),
"symbol2",
BLANK ()
)
)

This formula creates a new column called "Symbol" in Table2. It checks the conditions you specified:

• If "weekday_type" in Table2 is "Weekend," it assigns "symbol1."
• If "weekday_type" is "weekday" in Table2 and the related "ref date" in Table1 (Calendar table) is blank, it assigns "symbol2."

If none of these conditions are met, it assigns a blank value.

1. After entering the formula, press Enter to create the calculated column.

Now, your Table2 should have a new column called "Symbol" with the desired values based on your conditions.

1. You can then use this "Symbol" column in your visuals to display the desired output.

Remember to adjust column and table names to match your actual data model. This DAX formula assumes that you have a one-to-many relationship between Table1 and Table2, as indicated by your table link type.

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

Hi 123abc ,

The weekday_type is coming from Calendar so, the second IF statement as you write it will not work.

databot_kd if you add a calculated column in Transaction table the syntax should be more like

VAR _week_type = RELATED ( 'Date'[weekday_type] ) RETURN IF ( _week_type = "Weekend", "symbol1", IF ( _week_type = "Weekday" || ISBLANK ( 'Transaction'[ref date] ), "symbol2", BLANK () ) )

As the "ref date" could be blank, an or condition is more appropriate. else make sure that your transaction table is properly fill. 

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