Forum Discussion

cgardyne's avatar
cgardyne
Icon for Helper I rankHelper I
9 years ago

Average of Measure in Direct Query mode

Currently I have a measure calculating the days between the start and end dates and getting a value in days:

Life In Days = DateDiff(Max([StartDateNzt]), Max(LoanStatusHistories[CreateDateNzt]), DAY)

There's only one StartDateNzt per loan, but it can move through multiple states hence the Max(LoanStatusHistories[CreateDateNzt]) to get the last state change date.

This works fine per loan, but now I want to get an average of this measure.

 

I've tried just doing an averagex:

Life In Days = AverageX(Loans, DateDiff(Max([StartDateNzt]), Max(LoanStatusHistories[CreateDateNzt]), DAY))

but this gives me an error saying "AVERAGEX is not supported in this context in DirectQuery mode".

 

So I'm wondering if there's a way to do this?

 

Thanks,

Col.

Need Help

11 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi cgardyne,

     

    There are some limitations when you use dax formula on "direct query" mode, you can try to use measure to instead.

     

    Steps:

    1. Turn on the "allow unrestricted measures in directquery mode" option, (settings -> DirectQuery -> allow unrestricted measures in directquery mode)
    2. Use measure to calculate this result.

     

    Life In Days = AverageX(Loans, DateDiff([StartDateNzt], LoanStatusHistories[CreateDateNzt], DAY))

     

    Regards,

    Xiaoxin Sheng

    • cgardyne's avatar
      cgardyne
      Icon for Helper I rankHelper I
      9 years ago

      hi Anonymous.

       

      Thanks, now the averagex is allowed, but it doesn't work quite right.

       

      I think the problem lies with the table LoanStatusHistories. This has a 1 to many relationship with Loans.

       

      So for each loan in the Loans table, I need to get the latest row based on the field CreateDateNzt from LoanStatusHistories and do a DateDiff with the Loans[StartDateNzt].

       

      So I then added Max around the LoanStatusHistories[CreateDateNzt] so the measure now looks like the following:

       

      Life In Days = AverageX(Loans, DateDiff([StartDateNzt], Max(LoanStatusHistories[CreateDateNzt]), DAY))

      When I add the AverageX measure below it doesn't filter down. Basically no matter what filter I apply it always gives me the same result?

       

      Thanks,

      Colin

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Hi cgardyne,

         

        Since I'm not very clear for you table struct and relationships, can you share a part of your sample data to test?

         

        Regards,

        Xiaoxin Sheng