Forum Discussion

atjt217's avatar
atjt217
Icon for Helper III rankHelper III
4 years ago
Solved

Last order date by Customer

Hello, 

I would like to ask for help figuring out which formula to use on my report. I got 2 tables where i need to get the last order date from the order table to be able to see when is the last time our Customer made an order: 

 

Sample

I appreciate your help! 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi atjt217 ,

    All DAX functions are supported for DirectQuery models. However, not all functions are supported for all formula types. Some functions like CALCULATE are supported in measure and query formulas only. For details: DAX Formula Compatibility in DirectQuery Mode

     

    In your scenario, please new a measure rather than creating a calculated column using above formula.

     

    Please refer to my pbix file to see if it helps you.

    Create a measure.

     

    Measure =
CALCULATE (
    MAX ( 'Sheet2$'[Date] ),
    FILTER (
        ALL ( 'Sheet2$' ),
        'Sheet2$'[Customer ID] = SELECTEDVALUE ( 'Sheet2$'[Customer ID] )
    )
)

     

    Then create relationships between the tables.

     

    If I have misunderstood your meaning, please provide your desired output with more details.

     

    Best Regards

    Community Support Team _ Polly

     

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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi atjt217 ,

    All DAX functions are supported for DirectQuery models. However, not all functions are supported for all formula types. Some functions like CALCULATE are supported in measure and query formulas only. For details: DAX Formula Compatibility in DirectQuery Mode

     

    In your scenario, please new a measure rather than creating a calculated column using above formula.

     

    Please refer to my pbix file to see if it helps you.

    Create a measure.

     

    Measure =
CALCULATE (
    MAX ( 'Sheet2$'[Date] ),
    FILTER (
        ALL ( 'Sheet2$' ),
        'Sheet2$'[Customer ID] = SELECTEDVALUE ( 'Sheet2$'[Customer ID] )
    )
)

     

    Then create relationships between the tables.

     

    If I have misunderstood your meaning, please provide your desired output with more details.

     

    Best Regards

    Community Support Team _ Polly

     

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

  • VahidDM's avatar
    VahidDM
    Icon for Super User rankSuper User
    4 years ago

    Hi atjt217 

     

    You can add a new column with dax to the Customer Table with this formula:

    LastOrder = 
CALCULATE (
    MAX ( 'Order Table'[Date] ),
    FILTER (
        ALL ( 'Order Table' ),
        'Order Table'[CustomerID] = EARLIER ( 'Customer Table'[CustomerID] )
    )
)

     

    Output:

     

     

     

     

    Sample file attached, please download.

     

     

    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
    Appreciate your Kudos!!
    LinkedIn:     www.linkedin.com/in/vahid-dm/

     

     

    • atjt217's avatar
      atjt217
      Icon for Helper III rankHelper III
      4 years ago

      Hello VahidDM, 

       

      I tried it out but im getting this error message: 

      "Function 'CALCULATE' is not allowed as part of calculated column DAX expressions on DirectQuery models."