Forum Discussion

jonclay's avatar
jonclay
Helper IV
4 years ago
Solved

Issue with a Measure

Hi everyone I've written the following measure that pulls back the latest date of a sale from a list of sales. I then place this in my Power BI report against a contact, so it effectively shows th...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi

     

    You needed to define the Earlyest date in the variable, so it is defined in the Scope's Row context.

     

    Bellow the two formulas

     

     

    Earliest_Date = 
MINX (
    FILTER (
        all_relevant_transactions,
        all_relevant_transactions[statuscodename] = "Paid"
    ),
    all_relevant_transactions[si_transactiondateofpayment]
)

     

     

     

    Earliest_Amt = 
VAR Early_Date = [Earliest_Date]

RETURN
CALCULATE(
    MIN(all_relevant_transactions[destcodenet]),
    FILTER(
        all_relevant_transactions,
        all_relevant_transactions[si_transactiondateofpayment] = Early_Date
    )
)

     

    Kind regards,
    José
    Please mark this answer as the solution if it resolves your issue.
    Appreciate your kudos! 🙂

jonclay's avatar
jonclay
Helper IV
4 years ago

Hi José

Many thanks for your reply.

I've tried making these changes to my Measures but I'm now getting a date on the Earliest_Amt Measure instead of a numerical value. I've tried changing the Measure to the one below, but then I just get the same as I did before.

 

CALCULATE
    ( MIN(all_relevant_transactions[destcodenet]),
    FILTER(
        all_relevant_transactions,
        all_relevant_transactions[transactiondateofpayment] = [Earliest_Date]
        )
    )

Do you know what I'm doing wrong?

Many thanks
Jon
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Could you provide a sample from your data? Or maybe a PBIX file?

  • jonclay's avatar
    jonclay
    Helper IV
    4 years ago

    Hi there

    Thank you for your reply. Hopefully the images below will show you what is happening:

    The Date Measure is working correctly as it's pulling out 26/07/2007 as the earliest date for this contact:


    However, the Amount Measure is still pulling out £100 as it is the lowest amount. I completely understand Jose's reasons in the comments above, but I'm still getting £100 when I change the Measure.

    Could it be because I'm not referencing the SerialID field (i.e. the unique contact ID) anywhere in the Measures?

    Many thanks
    Jon



    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Ok

      That will depend on where you are visualizing the Measure, because the filter and row context will be diferent.

       

      Either way, alter your Earliest_Amt to this, so that will remove the Context from all columns except the Contact:

       

      Earliest_Amt =
MINX (
    FILTER (
        ALLEXCEPT ( all_relevant_transactions, all_relevant_transactions[Contact] ),
        all_relevant_transactions[statuscodename] = "Paid"
    ),
    all_relevant_transactions[destcodenet]
)
      • jonclay's avatar
        jonclay
        Helper IV
        4 years ago

        Thank you.

         

        I'm visualising the data as per below:

         

        I'm basically just dragging the Date measure into the Table and it's automatically putting the earliest Transaction Date against the correct contact.

         

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Ok

      That will depend on where you are visualizing the Measure, because the filter and row context will be diferent.

       

      Either way, alter your Earliest_Amt to this, so that will remove the Context from all columns except the Contact:

       

      Earliest_Amt =
MINX (
    FILTER (
        ALLEXCEPT ( all_relevant_transactions, all_relevant_transactions[Contact] ),
        all_relevant_transactions[statuscodename] = "Paid"
    ),
    all_relevant_transactions[destcodenet]
)