Lookup value from related table based on a condition in another column in the related table

[Gingerjeans88]

Hi all,

I have two related tables, one called Quote and the other Quote History. They are related with cardinality many:1 (Many Quote History items to one Quote). 

Quote

QUOTE ID	CUSTOMER
Q123	Test Customer
Q456	Example Client

Quote History

QUOTE ID	STAGE	STAGE DURATION (MINUTES)
Q123	1) Quote Request	5
Q123	2) Shipping Estimate	7
Q456	1) Quote Request	1
Q456	2) Shipping Estimate	13

I want to lookup and return, in the Quote table, the Stage Duration for the Quotes in each stage, ie. achieve the below. Essentially, 'where stage =  1) quote request, display the value in stage duration for that quote id).

QUOTE ID	STAGE 1 DURATION	STAGE 2 DURATION
Q123	5	7
Q456	1	13

I tried 'related(column name) but as I am wanting the calculated column on the 'one' side and not the 'many' side, this doesn't seem possible and I am at a loss? This should be so simple. Please help I am desperate to complete this by tomorrow!

[Ashish_Mathur · Accepted solution]

Hi,

These calculated column formulas work

=CALCULATE(MIN(quote_history[STAGE DURATION (MINUTES)]),FILTER(quote_history,quote_history[STAGE]="1) Quote Request"&&quote_history[QUOTE ID]=EARLIER(Quote[QUOTE ID])))

=CALCULATE(MIN(quote_history[STAGE DURATION (MINUTES)]),FILTER(quote_history,quote_history[STAGE]="2) Shipping Estimate"&&quote_history[QUOTE ID]=EARLIER(Quote[QUOTE ID])))

Hope this helps.

[Gingerjeans88]

Hi Asish,
Thank you SO much for your help!
Any idea why it is returning all blanks though? FYI I have just realised my two quote ID fields are called two different things in the schema but they’re related as primary key so that shouldn’t matter should it?

[Ashish_Mathur]

Hi,

As you can see fromy screenshot, the calculated column formula is working very well.  I cannot say why it is not working for you.  Correct for spelling mistakes, if any.

[v-diye-msft]

Hi @Gingerjeans88 

Please use the measure below:

Stage 1 = CALCULATE(MIN('Quote History'[STAGE DURATION (MINUTES)]),FILTER('Quote History',[STAGE]="1) Quote Request"),VALUES('Quote History'[QUOTE ID]))

Stage 2 = CALCULATE(MIN('Quote History'[STAGE DURATION (MINUTES)]),FILTER('Quote History',[STAGE]="2) Shipping Estimate"),VALUES('Quote History'[QUOTE ID]))

 

Pbix attached,

[parry2k]

@Gingerjeans88 in matrix visual

QuoteId -> on row from Quote Table

Stage -> On column

Stage Duration (minutes) -> On value

and you will get what you are looking for

[Gingerjeans88]

Thank you so much for the qukck reply @parry2k !

However, my eventual visual is to display something else, this is just a stepping stone. I will then calculate the total minutes per quote and count how many of them met the service level agreement minutes threshold of 1020. 

For my original query, I was wanting to create calculated columns to show the stage duration minutes, are you able to help with this?

[parry2k]

@Gingerjeans88 well what you explained in original post, I posted teh solution for that, if you provide further details and expected output, we will work on that. 🙂

SHare sample data with expected output, it will help to get the solution.

[Gingerjeans88]

Sorry @parry2k my fault, I am not explaining myself very well! 

I have two tables, and in the smaller 'Quote' table I want a new column that contains a value I have looked up from the other (related) table. The Quote table has distinct values and the Quote History has duplicates, hence wanting to return the value within a new column in the Quote (distinct) table. 

So the curent tables are as below:

Quote

QUOTE ID	CUSTOMER
Q123	Test Customer
Q456	Example Client

Quote History

QUOTE ID	STAGE	STAGE DURATION (MINUTES)
Q123	1) Quote Request	5
Q123	2) Shipping Estimate	7
Q456	1) Quote Request	1
Q456	2) Shipping Estimate	13

And my expected result is as below

QUOTE ID	STAGE 1 DURATION	STAGE 2 DURATION
Q123	5	7
Q456	1	13

I want to lookup and return, in the Quote table, the Stage Duration for the Quotes in each stage, ie. achieve the below. Essentially, 'where stage =  1) quote request, display the value in stage duration for that quote id).

[parry2k]

@Gingerjeans88 I'm not sure how it is different that original post, with origina reply, just filter  for stage 1 and it will work. I guess when you say stage 1 you mean 1) Quote Request

[Gingerjeans88]

I do, yes, 1) Quote Request.

I just don't know what DAX to write for the calculated column? Do you mean physically filter or use filter in the DAX? 

Sorry for all the questions!

[parry2k]

@Gingerjeans88 you can surely add filter on visual level or page level, depending on your needs, just drop Stage column there and filter for "Stage 1" but if you want in measure, add following measure

use below measure on value section of matrix and that will do it.

Stage 1 Minutes = 
CALCULATE ( SUM ( QuoteHistory[Stage Minutes] ), QuoteHistory[Stage] = "1) Quote Request" )

[Gingerjeans88]

Thanks again. Just to clarify though, I am wanting to know how to generate a calculated column in my data model and what DAX to add (filter related? lookup value?) to return the Stage 1 Duration value for each QuoteID in my Quote table, where the 'Stage' in Quote History table = Stage 1 (Quote Request)....not add a matrix visual. As I will need to use the column for further visuals.

Sorry if I am being dumb. 

[parry2k]

@Gingerjeans88 the measure calculation I gave , it will work any visual. Matrix visual was just an example. Try that calculation wherever you want to use it and then tell what is not working. I want you to try what you have before asking further question and explain what is not working. 

[Gingerjeans88]

Hi there,

Tried this as dax for my calculated column and it returned all blanks. I know each of the rows in the related table have values in the Stage Duration column. 

Thanks

[parry2k]

@Gingerjeans88 add it is a MEASURE

[Gingerjeans88]

I am so confused, sorry! 

I know you are saying to add it as a measure, but I want a simple row-by-row calculation (lookup, in this case), not an aggregation like what a measure would achieve. 

I literally want to find the stage 1 duration, for a quote ID, from the quote history table, and populate it in the new stage 1 duration column in the quote table. 

Sorry to keep repeating myself, not trying to be difficult.