Getting a string value based on the max date of another value

[jonclay]

Hi everyone

I wonder if you could please help me with the following query?

I made a post yesterday and received a solution to help me find the latest date of some payments per customer (so, if a customer had paid each year on 1st December for the past 10 years, the Measure would bring back the date of 01/12/2021 for that particular customer). The Measure that I used is:

Last Gift =

CALCULATE(

MAX ('Transactions'[dateofpayment]),

ALLSELECTED ('Transactions'),

VALUES ('Transactions'[contactid])

)

However, these is also a TransactionType field on the Transactions table, and I would like to pull back the TransactionType relating to the latest payment as per the Measure above.

I've tried the following Measure but it seems to pull back the TransactionType value of the first ever payment. So, if someone has paid 10 times and the first time was by Cash and the others, including the latest payment, were paid by Cheque, the Measure will being back the string of Cash incorrectly (i.e. it should being back Cheque because the last payment was paid by Cheque):

Last Type =

CALCULATE(

MAX ('Transactions'[transactiontype]),

ALLSELECTED ('Transactions'),

VALUES ('Transactions'[dateofpayment])

)

Any help would be much appreciated.

Many thanks
Jon

[v-henryk-mstf · Accepted solution]

Hi @jonclay ,

What type of data source are you connecting in which mode? For this type of error message, it may not be a formula creation issue. Rather, it has to do with the access rights to the data source or your connection mode.

Such as below:

Similar solutions:

powerbi - Power BI Direct Query : Couldn't load the data for this visual - Stack Overflow

Solved: Couldn't load data for this visual - Microsoft Power BI Community

If the problem is still not resolved, please provide the relevant test data, so that I can answer for you as soon as possible. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[NickolajJessen]

Hi, does this help you?

[David_QH]

This helped me with a different problem to the OP.  Thanks Mate

[jonclay]

Hi Nickola

Thanks so much. That looks like it should work for me (very logical), but I get the following error when I try to run it:

Do you know why that might be?

Many thanks

Jon

[NickolajJessen]

Sorry, that error message isn't familiar to me.

A quick google shows users having issues when using DirectQuery. Is this something you are using as well?

You should double check you didn't leave out any parenthesis, otherwise try to look around the causes of the error message.