DirectQuery Model Many to many relationship vlookup,related or calculated function

[Haidarius]

Hello everyone,

I have two tables (A and B) with many to many relationship between them using column called "RelationID"

I am trying to use a column (lets call it Column_I_want) from table B to do a calculation in table A for a new column, I thought I can use RELATED and call that other column but realized it wont work for a many to many, so I went ahead after doing some research and tried retrieving that other column using the following expression:

New_column_retrieval =

VAR LID = Table_A[RelationID]

RETURN

    CALCULATE(

        MAX( 'Table_B'[Column_I_want] ),

        'Table_B'[Relation_ID] = LID

    )

but it won't work since the CALCULATE function is not allowed as part of calculated column DAX expressions on DirectQuery models.

** by the way I do have another common column that can make it a many to one relationship but cannot activate it since table_A is connected with a many to 1 relationship and apparently table B and C seem to be connected through A hence id have to delete the relationship between both in order for me to go ahead with that**

Any help would be greatly appreciated.

[v-chenwuz-msft · Accepted solution]

Hi @Haidarius ,

In direct query mode, it is not possible to create a colum. So only measure can be created.

A measure like this:

New_column_retrieval =

VAR LID = max(Table_A[RelationID])

RETURN

    CALCULATE(

        MAX( 'Table_B'[Column_I_want] ),

        filter(all('Table_B'),[Relation_ID] = LID

    )

Best Regards

Community Support Team _ chenwu zhu

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