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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
JackWhelpton
New Member

Retrieve data from related table with filter conditions

My current data structure has a relationship where our Client Database which holds key information (name, dob etc) is the central node and various other tables of data relate to this via a "ClientRecord" field to allow for data to be related cross tables.

 

One of these related tables is our "Referral" information and is that particular entry point into our system, note that each client could have multiple referrals if they engage with us multiple times so its important that I collect this data against their referal, not their client record.

 

I have a second table that holds assessment results, this is related to the main client database with the same ClientRecord column. What I am wanting to achieve is find the first assessment for each referral which will lookup based upon the ClientRecord value and then the first record after the creation of that referral. I am then wanting to do the same with the Last record after the referral which should show the score for their First Assessment and then their Last Assessment since their referral.

 

Another example where I am wanting to do the same thing pretty much is with our recorded activities, I want to retrieve the first activity creation date after the individual referral creation date. The aim is to be able to calculate the amount of time between referral and first contact for each referral record.

 

I am not sure if I am explaining this properly but I have been trying to sort this for a while and made no progress at all. Any help would be appreciated.

 

Edit - My latest attempt which just loads forever and never does anything: = Table.AddColumn(#"Renamed Columns", "Custom", (w) => Table.SelectRows(#"EDE-Q 6 0 Form Responses", (x) => x[ClientRecord] = w[ClientRecord] and x[Created] >= w[Created]))

1 ACCEPTED SOLUTION
JackWhelpton
New Member

Update- I believe I have managed to sort the issue with the below DAX query: 

 
LastEDEQScore = var clientRecordID = 'Online Referral Form Responses'[ClientRecordId] var fromDate = 'Online Referral Form Responses'[Created] var results = MAXX(filter('EDE-Q 6 0 Form Responses','EDE-Q 6 0 Form Responses'[ClientRecordId] = clientRecordID && 'EDE-Q 6 0 Form Responses'[Created] >= fromDate),'EDE-Q 6 0 Form Responses'[Id]) var record = MAXX(Filter('EDE-Q 6 0 Form Responses', 'EDE-Q 6 0 Form Responses'[Id] = results),'EDE-Q 6 0 Form Responses'[GlobalEDEScore])   return record
 
Seems to be working as intended!

View solution in original post

1 REPLY 1
JackWhelpton
New Member

Update- I believe I have managed to sort the issue with the below DAX query: 

 
LastEDEQScore = var clientRecordID = 'Online Referral Form Responses'[ClientRecordId] var fromDate = 'Online Referral Form Responses'[Created] var results = MAXX(filter('EDE-Q 6 0 Form Responses','EDE-Q 6 0 Form Responses'[ClientRecordId] = clientRecordID && 'EDE-Q 6 0 Form Responses'[Created] >= fromDate),'EDE-Q 6 0 Form Responses'[Id]) var record = MAXX(Filter('EDE-Q 6 0 Form Responses', 'EDE-Q 6 0 Form Responses'[Id] = results),'EDE-Q 6 0 Form Responses'[GlobalEDEScore])   return record
 
Seems to be working as intended!

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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