Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
4 years ago

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

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

IDFECHA1CODEDAX COLUMN
104/01/2020OF04/01/2020
201/05/2021FRI01/05/2021
3 IT04/01/2020
4 IT01/06/2022
501/04/2020FRI01/04/2020

tabla2

IDFECHA2CODE
104/01/2020OF
201/05/2021FRI
304/01/2020IT
4 01/06/2022IT
501/04/2020FRI

Is there a way to do this in DAX?

Greetings

1 Reply

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    4 years ago

    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.