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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ktp_99
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
v-yuezhe-msft
Employee
Employee

@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.

View solution in original post

7 REPLIES 7
v-yuezhe-msft
Employee
Employee

@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.

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.

 

 

 

@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.

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.

@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.

Thank you Lydia..   That worked.

ktp_99
Frequent Visitor

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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