Recalculate RELATED column in imported table based on Direct Query table

[Anonymous]

I am having an issue with a composite model where my table that is using a calculated column to get data from a table in Direct Query is not updating when a slicer is applied, it will only update when a refresh occurs. 

My dimension table contains all the IDs that are in the Daily Data table, these are calculated based on a rule and not imported from the table, but do not have the DateTime value for it. The DateTime needs to be looked up by ID in the Daily Data table and update when there is a new ID in the Daily Direct query table. 

My looks like this:

relationships

and my Dax to get the value from the daily table via calculated column is:

DateTime = IF('Dimension (Import)'[NZDT]=BLANK(),MAXX(RELATEDTABLE('Daily Data (Direct Query)'),'Daily Data (Direct Query)'[DateTime]),'Dimension (Import)'[NZDT])

Note: I have used RELATEDTABLE as RELATED has a bug in direct query as noted here. I have also tried LOOKUPVALUE but this has the same issue.

[v-lid-msft]

Hi @Anonymous ,

For tables in import mode,  calculated columns are computed during the database processing and then stored in the model. So In your scenario, we cannot make the calculated column dynamically by DirectQuery table or slicer.

We can try to use a measure to show the datetime dynamically:

DateTime_Measure =
IF (
    SELECTEDVALUE ( 'Dimension (Import)'[NZDT] )
        = BLANK (),
    CALCULATE (
        MAX ( 'Daily Data (Direct Query)'[DateTime] ),
        'Daily Data (Direct Query)'[id]
            = SELECTEDVALUE ( 'Dimension (Import)'[id] )
    ),
    SELECTEDVALUE ( 'Dimension (Import)'[NZDT] )
)

Best regards,

[Anonymous]

Hi @v-lid-msft,

Thanks for this, however since I want to be able to display datetime information on the x axis of a graph I don't believe your solution will work. 

Is my only option to put my dimension table in DQ mode?

There is no way to incrementally load DQ data while keeping the majority of data in Import mode and only getting the latest data and appending to my dimension table? 

The reason for this is that this is IoT data that needs to come in as soon as its in the database and we are seeing much-degraded performance when loading all the data in DQ mode (>100 second queries). 

Cheers, 

MG