Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
masplin
Impactful Individual
Impactful Individual

Looking up data from another model in a joint model

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

7 REPLIES 7
sturlaws
Resident Rockstar
Resident Rockstar

Hi @masplin,

 

could you provide the code for you calculated column?

 

-sturla

 

masplin
Impactful Individual
Impactful Individual

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
Impactful Individual
Impactful Individual

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])

 

 

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
Impactful Individual
Impactful Individual

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.

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
Impactful Individual
Impactful Individual

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 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors