topic Trying to Get a Max Rate for a Customer, filter that rate and see all open Accounts for Customer in DAX Commands and Tips

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

RRush — Tue, 07 Jan 2025 01:29:14 GMT

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

)

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

some_bih — Tue, 07 Jan 2025 06:43:05 GMT

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.

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

Anonymous — Wed, 08 Jan 2025 01:46:02 GMT

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.

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

BeaBF — Wed, 08 Jan 2025 11:19:56 GMT

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

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

RRush — Thu, 16 Jan 2025 13:48:35 GMT

BBF,

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