Average of Measure in Direct Query mode

[cgardyne]

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.

[v-shex-msft]

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]

hi @v-shex-msft.

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

[v-shex-msft]

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]

No problem @v-shex-msft. What format would suit you best?

[v-shex-msft]

Hi @cgardyne,

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

Regards,

Xiaoxin Sheng

[cgardyne]

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).

[v-shex-msft]

Hi @cgardyne,

I got the sample file, but I can't find the "StartDateNzt" column/measure, can you fix it?

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

Since your original table only has three records, when at the Total row, table visual will try to calculate summary result with column values which from whole original table.(the calculation may similar as: Calculate(sum(Loans[Amount]),ALL(Loans))

Regards,

Xiaoxin Sheng

[cgardyne]

Hi @v-shex-msft,

I've added StartDateNzt and added the measure for Loan Life in Days as you'll see in the tables on the right.

Thanks,

Col

[v-shex-msft]

Hi @cgardyne,

>>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?

You can modify the cross filter direction option to both to fix this issue.

Regards,

Xiaoxin Sheng

[cgardyne]

hi @v-shex-msft,

In my example data I managed to change the filter direction and it worked fine!

But the problem is in my live data I can't change the filter direction?

[v-shex-msft]

Hi @cgardyne,

Not sure why you can't modify the relationship, but it seems work on my side.

Maybe you can try to remove the original relationships and re-create it.

Regards,

Xiaoxin Sheng