Need Help with Calculate

Hi Anonymous 
Sorry for the late reply as I was too busy with loads of projects in my company.

There are some corrections that the company has made. So now, instead of the Balance Sheet table, we are using COADetails_New table which is connected to the Fact table directly with a "One to Many" relationship as shown below in the data model - 

Also, we have got the DAX measure working but still it has got a hard-coded portion in it in as a filter
// COADetails_New[AccountID] >= "400000" && COADetails_New[AccountID] <= "957090")

See the screenshot below - 
 

We don't want this portion to be hard-coded.
Instead we want to read it from the table itself i.e. COADetails_New table.

In the COADetails_New table, here are a snap of the main columns that can be taken into account to perform the required operation - 

In our current DAX measure we have hard-coded the AccountID for a range between 400000 and 957090 (as mentioned in my first post). Also we don't have the columns Start Account and End Account in COADetails_New table like we had before in the Balance Sheet table (I mentioned in my first post).

Now, how can I put the range dynamically instead of hard-coding ? Remember, you can't use the totalling column from COADetails_New table and break it into Start and End account because it remains outdated, the client never updates this.

Hi Gazi_Sohan ,

We can create two calculated columns on table COA Details.

Column 1 = MID([Operator Between Totaling], 1, SEARCH("..", [Operator Between Totaling], , 1) - 1) 

Column 2 = MID([Operator Between Totaling], SEARCH("..", [Operator Between Totaling], , 1) + 2, LEN([Operator Between Totaling]))

Then we can update the measure.

Dynamic Accounts = 
var selectedrecord = SELECTEDVALUE('COA Summary'[Account ID])
var _Description=SELECTEDVALUE('COA Summary'[Account Description])
var _a=CALCULATE(MAX('COA Details'[Column 1]),FILTER(ALL('COA Details'),'COA Details'[Account Description]=_Description))
var _b=CALCULATE(MAX('COA Details'[Column 2]),FILTER(ALL('COA Details'),'COA Details'[Account Description]=_Description))
var _c=CALCULATE(MAX('COA Details'[Column 1]),FILTER(ALL('COA Details'),'COA Details'[Account Description]=_Description))
var _d=CALCULATE(MAX('COA Details'[Column 2]),FILTER(ALL('COA Details'),'COA Details'[Account Description]=_Description))
RETURN
SWITCH(
    selectedrecord,
    106,
    CALCULATE(
        SUM(Transactions[Amount]),
        ALL('COA Details'),
        'COA Details'[Account ID] >= CONVERT(_a,INTEGER)
        && 'COA Details'[Account ID] <= CONVERT(_b,INTEGER)
    ),
    206,
    CALCULATE(
        SUM(Transactions[Amount]),
        ALL('COA Details'),
        'COA Details'[Account ID] >= CONVERT(_c,INTEGER)
        && 'COA Details'[Account ID] <= CONVERT(_d,INTEGER)
    ),
    CALCULATE(
        SUM(Transactions[Amount]),
        'COA Details'[Account ID] >= 'COA Details'[Operator Between Start]
        && 'COA Details'[Account ID] <= 'COA Details'[Operator Between End]
    )
)

Best Regards,

Neeko Tang

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