Forum Discussion
Last value for column in summary table
Hi,
I have a scenario where I have created a summery of customers, OrderCount,sumofmoneytheyowe,lastpaymentrejecteddate. I have a column as decline reason which is row based and I want to created a column which gives me lastdecline reason based on lastpayment rejection date. I have tried LastNonBlank, this summarises and gives all decline reason. Also tried lookup but not clear why it stats a loop is created. I cant use more than one columns or measure for this and so looking to get a solution in one DAX .
Can some one please help me on this, let me know if you have questions.
Hi NehaVageriya,
The data type of "decline reason" is text, we are not summarize directly. Indirectly, I add a index column in Query Edit. Create a calculte column to get the index according the highest_rejection. Then we can lookup the decline reason based on the index column and calculated column.
MAX = CALCULATE(MAX(Table7[Index]),FILTER(ALLEXCEPT(Table7,Table7[customerId]),Table7[Payment rejection]=MAX(Table7[Payment rejection])))
The following formula returns the declined reason.
Table 3 = DISTINCT(SELECTCOLUMNS(Table7,"5",LOOKUPVALUE(Table7[decline reason ],Table7[Index],Table7[MAX])))
Finally, combibe other fields, create a new table using the formula below and get expected result.Table 4 = ADDCOLUMNS(SUMMARIZE(Table7,Table7[customerId],"ordercount",CALCULATE(DISTINCTCOUNT(Table7[orderId]),ALLEXCEPT(Table7,Table7[customerId])),"highest_rejection",CALCULATE(MAX(Table7[Payment rejection]),ALLEXCEPT(Table7,Table7[customerId])),"totalamountown",CALCULATE(SUM(Table7[amount_owe]),FILTER(ALLEXCEPT(Table7,Table7[customerId]),Table7[Payment rejection]=MAX(Table7[Payment rejection]))),"lastrejectiondate",CALCULATE(MAX(Table7[payment_rejection_date]),FILTER(ALLEXCEPT(Table7,Table7[customerId]),Table7[Payment rejection]=MAX(Table7[Payment rejection])))),"declined reason",SELECTCOLUMNS(Table7,"5",LOOKUPVALUE(Table7[decline reason ],Table7[Index],Table7[MAX])))
If you have any issue, please feel free to ask.
Best Regards,
Angelia
5 Replies
Your question is very hard to understand. Please have a look a this article that describes how to post a question in the forum that makes it easier for the others to answer: https://social.technet.microsoft.com/wiki/contents/articles/28212.how-to-ask-a-power-pivot-question-to-get-a-prompt-accurate-and-helpful-response.aspx
and redesign your question accordingly.
Also it will be easier to help if you can provide some dummy sample dataset. thanks!
Hi, apologies for not posting out clear question. reframing it with a small dataset.
I have a data where customer, orders are processed to deduct money they owe, and for different reasons payment doesnt go through,which is was my data looks like. below is that data.
customerId orderId Payment rejection amount_owe decline reason payment_rejection_date 1 1 1 20 declined_card 20/12/2016 1 2 1 25 declined_card 20/12/2016 1 1 2 25 expired card 21/12/2016 1 2 2 30 expired card 21/12/2016 What I have done is created a summary table with below
CustomerId,OrderCount,highest_rejections,Total_amount_owe,last_payment_date. what I am looking for is last_decline reason in summary screnario. When I use Lookup or LastNonblank it always gives me both the decline reasons stated for that customerid. Below is how my summary looks
customer_id ordercount highest_rejection totalamountown declined reason lastrejectiondate 1 2 2 55 expired card 21/12/2016 Please let me know if you have any more questions
Hi NehaVageriya,
The data type of "decline reason" is text, we are not summarize directly. Indirectly, I add a index column in Query Edit. Create a calculte column to get the index according the highest_rejection. Then we can lookup the decline reason based on the index column and calculated column.
MAX = CALCULATE(MAX(Table7[Index]),FILTER(ALLEXCEPT(Table7,Table7[customerId]),Table7[Payment rejection]=MAX(Table7[Payment rejection])))
The following formula returns the declined reason.
Table 3 = DISTINCT(SELECTCOLUMNS(Table7,"5",LOOKUPVALUE(Table7[decline reason ],Table7[Index],Table7[MAX])))
Finally, combibe other fields, create a new table using the formula below and get expected result.Table 4 = ADDCOLUMNS(SUMMARIZE(Table7,Table7[customerId],"ordercount",CALCULATE(DISTINCTCOUNT(Table7[orderId]),ALLEXCEPT(Table7,Table7[customerId])),"highest_rejection",CALCULATE(MAX(Table7[Payment rejection]),ALLEXCEPT(Table7,Table7[customerId])),"totalamountown",CALCULATE(SUM(Table7[amount_owe]),FILTER(ALLEXCEPT(Table7,Table7[customerId]),Table7[Payment rejection]=MAX(Table7[Payment rejection]))),"lastrejectiondate",CALCULATE(MAX(Table7[payment_rejection_date]),FILTER(ALLEXCEPT(Table7,Table7[customerId]),Table7[Payment rejection]=MAX(Table7[Payment rejection])))),"declined reason",SELECTCOLUMNS(Table7,"5",LOOKUPVALUE(Table7[decline reason ],Table7[Index],Table7[MAX])))
If you have any issue, please feel free to ask.
Best Regards,
AngeliaHi,
Thanks for solution. Unfortunately it didnt work exactly for me this way as. I was getting an error for circular dependency when i was using your DAX. However, you logic of generating an index was very good and I had not known that, using that I solved the issue. And so I am accepting the solution.
Thanks again
