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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors