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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.