Looking up data from another model in a joint model

[masplin]

Hi. Really stuck on this.  I have 2 models, one is a CRM system thatrecords client aquistion unti lthey start.  The other is a transaction system that takes over once they start.  My issue is that currently we have to record thestart date in theCRM system by looking up manaully in the transactional system and entering the data. Not suprisingly this is often wrong/out of date.  

The clients have the same account no in both systems. I created a joint model and thoguht it would be trivial to use the CRMaccount no to lookup the start date on the transaction client table and do a comparison.  However depsite creating an inactive relationship bewteen the 2 account numbers netiher calcuate(values(  or Lookupvalue produces any result if I try to add a column to the Client CRM table (which is blank as says (This table uses direct query and cannot be shown). The error is "Client CRM[PP Start Date] cannot be pushed to the remotedata source and canot be used in this scenario"  where Client PP[PP Start Date] is henew column i'm trying to create on the Client CRM table.

Surely there is a way to link the column data from the 2 models that have been joined in a model? It seems to work fine for measures.

Any advice apprecaited

Mike

[sturlaws]

Hi @masplin,

could you provide the code for you calculated column?

-sturla

[masplin]

So coudl I have create a modle form 2 joining Power BI publsihed datasets not using direct query as that seems to be my issue? 

[masplin]

Hi. The source is 2 other power BI models published to the service. 

I tried this to add a column to Client CRM of the start date for same clinet on Client PP (transactional)

PP Start Date = LOOKUPVALUE('Client PP'[Client Start Date],'Client PP'[Account Number],'Client CRM'[Account Number])

[sturlaws]

From the documentation of LOOKUPVALUE:

LOOKUPVALUE function (DAX) - DAX | Microsoft Learn

• This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

Why do you need it as a calculated column? Could you make do with a measure, and show the start date pr customer in a table visual?

Cheers,
Sturla

[masplin]

I'll try as a maeausre, but seemed obvious to just add an extra column as its static data and only needs calcuating once.   Can you build cvombined models without using direct query?  The underlying models are only updated every week so there is no "live" data anywhere.

[sturlaws]

If you are working with direct query, it is not static anyways, it will always be calculated by the query which is generated whenever you do something that filters a visual.

The alternativ to direct query is import mode, but I don't think you can switch from one to the other, you would have to create a new file.

[masplin]

I tried creating anew file, but it didnt give me an option of import mode when I choose PowerBI datasets just does Direct query automoatically. really irritating. I'll try to write a measure as that seems to work ok across tables