Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! 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]))
Regards,
Lydia
@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
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.
Regards,
Lydia
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.
Regards,
Lydia
Thank you Lydia.. That worked.
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |