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...
Need Help
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

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

    No problem Anonymous. What format would suit you best?

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Hi cgardyne,

       

      You can upload a pbix file with part of sample data and share us the link.

       

      Regards,

      Xiaoxin Sheng

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

         

        I've used Data Import mode to import some sample data from my local db into pbix - not sure how much you'll be able to see? 

         

        Basically Loan has multiple LoanStatusHistories which has a one to one to LoanStatus. Here's the link to the pbix - https://drive.google.com/file/d/0Bx18tlhBvGkFdHNwbHphMWUwSE0/view

         

        As you can see on the left are all the LoanStatusHistories for each Loan and on the right are 3 tables where I'm filtering the Loans down by LoanStatus[Display] - Active, Pre Approved and Awaiting User Verification. 

         

        You can see that summing the Amount in the tables is always $50,000 (total of all loans irrespective of filter).