cancel
Showing results for
Did you mean:
Highlighted
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

Accepted Solutions
Highlighted
Microsoft

## Re: Last value for column in summary table

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 5
Highlighted
Super User IV

## Re: Last value for column in summary table

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!

Highlighted
Super User VII

## Re: Last value for column in summary table

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

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.

Highlighted
Frequent Visitor

## Re: Last value for column in summary table

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

Highlighted
Microsoft

## Re: Last value for column in summary table

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

Highlighted
Frequent Visitor

## Re: Last value for column in summary table

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

Announcements

#### Microsoft Ignite

This will be a conference that you do not want to miss!

#### Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

#### August Community Highlights

Check out a full recap of the month!

#### August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

#### Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors