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
NehaVageriya
Frequent Visitor

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.

1 ACCEPTED SOLUTION

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

 

3.png

 The following formula returns the declined reason.

 

Table 3 = DISTINCT(SELECTCOLUMNS(Table7,"5",LOOKUPVALUE(Table7[decline reason ],Table7[Index],Table7[MAX])))

1.PNG

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


2.PNG

 

If you have any issue, please feel free to ask.

 

Best Regards,
Angelia

View solution in original post

5 REPLIES 5
NehaVageriya
Frequent Visitor

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.

 

customerIdorderIdPayment rejectionamount_owedecline reason payment_rejection_date
11120declined_card20/12/2016
12125declined_card20/12/2016
11225expired card21/12/2016
12230expired card21/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_idordercounthighest_rejectiontotalamountowndeclined reasonlastrejectiondate
12255expired card21/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])))

 

3.png

 The following formula returns the declined reason.

 

Table 3 = DISTINCT(SELECTCOLUMNS(Table7,"5",LOOKUPVALUE(Table7[decline reason ],Table7[Index],Table7[MAX])))

1.PNG

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


2.PNG

 

If you have any issue, please feel free to ask.

 

Best Regards,
Angelia

Hi, 

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

ImkeF
Super User
Super User

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

and redesign your question accordingly.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Also it will be easier to help if you can provide some dummy sample dataset. thanks!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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.