Trying to Get a Max Rate for a Customer, filter that rate and see all open Accounts for Customer

[RRush]

I am working on a report on which I want to filter our customers by a Rate Variance Group.  The customer would fall into a group based on the Max Rate Variance for all of their deposit accounts.  But, if a customer falls in that range I want to see all open accounts for the customer so I can evaluate the total relationship.  To do this I used "All Selected" so I can see all accounts for the customer.  The issue is that now I cannot filter the "Closed Accounts".

I have what you see in the example below and I have a slicer with a Rate Variance Groups is working.  

Example:

Customer Name         Account Number      Account Status              Max Rate Variance     Rate Variance for Deposit Account

ABC Company              123456                          Open                                  1.25                                       .75

ABC Company               85755                           Open                                  1.25                                     1.25

ABC Company               22225                           Closed                                  .25                                      .50

This is my current DAX establishing my slicer and my "Max Rate Variance" calculation.

MaxRateVariance =

VAR low = SELECTEDVALUE('Group-Deposit Rate Variance Group'[Low Range])

VAR high = SELECTEDVALUE('Group-Deposit Rate Variance Group'[High Range])

VAR cust = SELECTEDVALUE('Account Common'[Customer Number])

Var calc =

    MAXX(

        CALCULATETABLE(

        VALUES('Account Common'[Account Number]),

        ALLSELECTED('Account Common'[Account Number])

        ),

         [Rate Variance (Deposits)]

    )  

VAR result =

    SWITCH(TRUE(),

        calc >= low && calc <= high, calc

    )

Return

    IF(HASONEVALUE('Account Common'[Customer Name and Customer Number]),

        calc,

        BLANK()

    )

[RRush]

BBF,

      Thanks so much!  That worked and I appreciate your expertise!!!

[BeaBF · Accepted solution]

@RRush Hi! Try with:

MaxRateVariance =
VAR low = SELECTEDVALUE('Group-Deposit Rate Variance Group'[Low Range])
VAR high = SELECTEDVALUE('Group-Deposit Rate Variance Group'[High Range])
VAR cust = SELECTEDVALUE('Account Common'[Customer Number])
VAR calc =
MAXX(
CALCULATETABLE(
VALUES('Account Common'[Account Number]),
REMOVEFILTERS('Account Common'[Account Number]),
'Account Common'[Account Status] = "Open"
),
[Rate Variance (Deposits)]
)
VAR result =
SWITCH(
TRUE(),
calc >= low && calc <= high, calc
)
RETURN
IF(
HASONEVALUE('Account Common'[Customer Name and Customer Number]),
calc,
BLANK()
)

Then, create a Separate Measure to Filter Closed Accounts:

IncludeInReport =
IF(
'Account Common'[Account Status] = "Open",
1,
0
)

Add this measure as a visual-level filter with the condition IncludeInReport = 1

BBF

[v-zhengdxu-msft]

Hi @RRush 

Could you please provide more raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[some_bih]

Hi @RRush without model and other details, like relationships it is hard to spot issue. Especially, as your measure is using function ALLSELECTED (bit a "special" function suggested to be used in visual).

Nevertheless, check your measure definition [Rate Variance (Deposits)] or provide simple file with input and expected output for possible solutions.