Forum Discussion

jonclay's avatar
jonclay
Helper IV
4 years ago
Solved

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

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

Need Help

5 Replies

    • jonclay's avatar
      jonclay
      Helper IV
      4 years ago

      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's avatar
        NickolajJessen
        Solution Sage
        4 years ago

        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.



    • David_QH's avatar
      David_QH
      Advocate II
      1 year ago

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

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support
    4 years ago

    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.