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.
I've tried MATCH, LOOKUPVALUE, but not successful.
Appreciate your help!
Go to 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]))
View solution in original post
In Excel I can use functions: LARGE and COUNTIF
Not sure equivalent in DAX for above statement.
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.Regards,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.
@ktp_99,Please delete the relationships among the three tables.Regards,Lydia
Thank you Lydia.. That worked.
Join us in the third Triple A event!
It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.
Make sure you didn't miss any of the things that happened in the community in January!