Find equal or greater matching value from tableB [col a] and return tableB[col b]

[ktp_99]

Hello Community,

I am trying to create measure column "Qty_Done_By" based on following two tables. Basically trying to figure out when rcv_qty would be used up by based on use profile in table B.  

TABLE-A				TABLE-B
RCV	RCV_Qty	RCV_Cum_Qty		USE_BY	USE_Qty	USE_Cum_Qty		Qty_Done_by
201824	120	120		201827	145	145		201827
201825	360	480		201828	274	419		201829
201826	1794	2274		201829	258	677		201836
201827	240	2514		201830	387	1064		201836
201828	960	3474		201831	289	1353		201839
201829	120	3594		201832	296	1649		201839
201830	480	4074		201833	215	1864		201841
201831	600	4674		201834	302	2166		201844
201832	1080	5754		201835	101	2267		201845
201833	360	6114		201836	257	2524		201847
201834	120	6234		201837	345	2869		201848
201836	120	6354		201838	310	3179		201848
201838	360	6714		201839	460	3639		201849
201839	240	6954		201840	372	4011
201840	240	7194		201841	150	4161
201842	1080	8274		201842	231	4392
201843	240	8514		201843	176	4568
201844	720	9234		201844	571	5139
201845	120	9354		201845	727	5866
201846	480	9834		201846	224	6090
201847	240	10074		201847	122	6212
201848	240	10314		201848	161	6373
201851	960	11274		201849	587	6960

I've tried MATCH, LOOKUPVALUE, but not successful.

Thanks &  

Appreciate your help!

[v-yuezhe-msft · Accepted solution]

@ktp_99,

Firstly, add an index column in TABLE_A and TABLE_B in Power BI Desktop query editor.



Secondly, merge the two tables in query editor.



Thirdly, create the following calculated column in the merged table.

Rank = RANKX(FILTER(Merge1,Merge1[USE_BY]>EARLIER(Merge1[USE_BY])),Merge1[USE_BY],,DESC,Dense)

Countrows = CALCULATE( COUNTROWS( Merge1 ),  ALL(Merge1), Merge1[USE_Cum_Qty]>= EARLIER(Merge1[TABLE_A.RCV_Cum_Qty] ))

Qty_Done_by = CALCULATE(LASTNONBLANK(Merge1[USE_BY],1),FILTER(Merge1,EARLIER(Merge1[Countrows])=Merge1[Rank]))

Regards,
Lydia

[ktp_99]

Thanks Lydia.

I am getting err in creating Countrows.  

"Column 'TABLE_A.RCV_Cum_Qty' cannot be found or may no be used in this expression.

[v-yuezhe-msft]

@ktp_99,

Please make sure that you expanded the column below in merged table in Power BI Desktop query editor.


Regards,
Lydia

[ktp_99]

Lydia,   

That's was the issue... after expanding it worked.

Another issue if I refresh data and get more rows for example in USE TABLE (more rows in use TABLE then rcv TABLE) then it seems merge and calculated column do not dynamically adjust. 

Err: Column TABLEA.index in table Merge1 contains blank values and this not allowed for columns on the one side of many-to-relationship or for columns that are used as the primary key of a table.

It is possible that both table will have not extracting same number of rows.

Thank you.

[v-yuezhe-msft]

@ktp_99,

Please delete the relationships among the three tables.


Regards,
Lydia

[ktp_99]

Thank you Lydia..   That worked.

[ktp_99]

In Excel I can use functions: LARGE and COUNTIF

=LARGE($H$2:$H$44,COUNTIF($J$2:$J$44,">"&C2))

Not sure equivalent in DAX for above statement.