Create a calculated column by comparing two dates from different tables in DirectQuery

[Syndicate_Admin]

Hello

I would like to create a calculated column in DirectQuery that sees if a particular record in table1 has the [date1] field not null. If it is non-null, take [date1] and if it is null, take [date2] from table2. Here is an example:

Table 1

ID	FECHA1	CODE	DAX COLUMN
1	04/01/2020	OF	04/01/2020
2	01/05/2021	FRI	01/05/2021
3		IT	04/01/2020
4		IT	01/06/2022
5	01/04/2020	FRI	01/04/2020

tabla2

ID	FECHA2	CODE
1	04/01/2020	OF
2	01/05/2021	FRI
3	04/01/2020	IT
4	01/06/2022	IT
5	01/04/2020	FRI

Is there a way to do this in DAX?

Greetings

[v-jingzhang]

Hi @data_operador1 

Are both tables in DirectQuery mode? I'm afraid it's not possible to add this calculated column to a DirectQuery table as it will hit the following model limitation of DirectQuery. 

Limitations in calculated columns: Calculated columns are limited to being intra-row, as in, they can only refer to values of other columns of the same table, without the use of any aggregate functions. See About using DirectQuery in Power BI. 

Instead, you can try using a measure to get the expected date result. Remember to add 'Table 1'[ID] and 'Table 1'[CODE] to the same visual as well as the measure. 

Date =
IF (
    ISBLANK ( SELECTEDVALUE ( 'Table 1'[FECHA1] ) ),
    CALCULATE (
        SELECTEDVALUE ( 'Table 1'[FECHA1] ),
        FILTER (
            ALL ( 'Table 2' ),
            'Table 2'[ID] = MAX ( 'Table 1'[ID] )
                && 'Table 2'[CODE] = MAX ( 'Table 1'[CODE] )
        )
    ),
    SELECTEDVALUE ( 'Table 1'[FECHA1] )
)

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.