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

 

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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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's avatar
    cgardyne
    Icon for Helper I rankHelper I
    9 years ago

    Hi Anonymous,

     

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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's avatar
    cgardyne
    Icon for Helper I rankHelper I
    9 years ago

    hi Anonymous,

     

     

    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?

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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