Forum Discussion

cgardyne's avatar
cgardyne
Helper 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...
Need Help
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
    Helper 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

      • cgardyne's avatar
        cgardyne
        Helper I
        9 years ago

        No problem Anonymous. What format would suit you best?