cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ktp_99 Frequent Visitor
Frequent Visitor

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

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    
RCVRCV_QtyRCV_Cum_Qty USE_BYUSE_QtyUSE_Cum_Qty Qty_Done_by
201824120120 201827145145 201827
201825360480 201828274419 201829
20182617942274 201829258677 201836
2018272402514 2018303871064 201836
2018289603474 2018312891353 201839
2018291203594 2018322961649 201839
2018304804074 2018332151864 201841
2018316004674 2018343022166 201844
20183210805754 2018351012267 201845
2018333606114 2018362572524 201847
2018341206234 2018373452869 201848
2018361206354 2018383103179 201848
2018383606714 2018394603639 201849
2018392406954 2018403724011  
2018402407194 2018411504161  
20184210808274 2018422314392  
2018432408514 2018431764568  
2018447209234 2018445715139  
2018451209354 2018457275866  
2018464809834 2018462246090  
20184724010074 2018471226212  
20184824010314 2018481616373  
20185196011274 2018495876960  

 

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

Thanks &  

Appreciate your help!

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

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

@ktp_99,

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


Secondly, merge the two tables in query editor.

2.PNG

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]))

3.PNG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 REPLIES 7
ktp_99 Frequent Visitor
Frequent Visitor

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

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.

Moderator v-yuezhe-msft
Moderator

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

@ktp_99,

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


Secondly, merge the two tables in query editor.

2.PNG

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]))

3.PNG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ktp_99 Frequent Visitor
Frequent Visitor

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

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.

 

 

 

Moderator v-yuezhe-msft
Moderator

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

@ktp_99,

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

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ktp_99 Frequent Visitor
Frequent Visitor

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

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.

Moderator v-yuezhe-msft
Moderator

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

@ktp_99,

Please delete the relationships among the three tables.
1.PNG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ktp_99 Frequent Visitor
Frequent Visitor

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

Thank you Lydia..   That worked.

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 274 members 2,998 guests
Please welcome our newest community members: