IF condition across multiple tables returns blank/null values

[databot_kd]

Hi community, 

please can you assist. I am trying to create a simple DAX if statement across two table linked. 

Data model setup:
Table1 - Calendar table 
Table2 - Transaction table 

Table link type:
Calendar  1 --->--- * Transaction 

Current table in PBI

Desired Output

logic i am trying to create:
if weekday_type = Weekend then symbol1 
if weekday_type = weekday & ref date is null then symbol2

Tried using RELATED function but only works for where there is a match between the two tables

@sergej_og 
@ced_f 

Any help would be appreciated
Thanks

[databot_kd · Accepted solution]

Thanks all. 
Only way i could solve it was to do a left join (merge) in power query using the caledar table as the base. 

[ced_f]

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.

[databot_kd]

Hi @ced_f ,

I tried your solution but still not getting the expected results...

[ced_f]

Hi @databot_kd ,

Not sure to understand what you are trying to achieve. The symbol you want to calculate is for Dimension or Fact table ? 

the lines for which you don't have the symbol mean that these dates from Calendar table (Dimension) don't have a matching row in Fact table. so it's logic to get an empty symbol as symbol is calculate from Fact table. 

[databot_kd]

@ced_f 

I just trying to flag two things:
1. If it is a weekday and there is no transaction date then Icon1
2. If it is a weekend then Icon2

My calendar table has the date in the first column and the weekday_type 
My transactions has the ref_date which is linked to the Calendar.date 

Would it be possible to replace empty then based on a condition?

[databot_kd]

the reason why i can see the blanks is cause i selected 'Show Items with No Data" 

[ced_f]

Hi @databot_kd ,

Can you try with something like this : 

Icon = VAR _weekday_type =
    SELECTEDVALUE ( 'Date'[weekday_type] )
RETURN
    IF (
        _weekday_type = "Weekend",
        "symbol1",
        IF (
            _weekday_type = "Weekday"
                && ISBLANK ( COUNT ( 'Transaction'[ref date] ) ),
            "symbol2",
            BLANK ()
        )
    )

but I don't understand well what you are trying to achieve. If i understand well, the icon2 is fill only if you don't have transactions dates on it ? is'nt it ? 

Regards

[123abc]

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.

[databot_kd]

Hi @123abc 

Thanks, but had no luck. 
Tried a simple DAX as per below

Symbol = IF ( RELATED('Calendar'[weekday_type]) = "Weekend", "symbol1", "ABC")

Screenshot out out put
Data model