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