DAX measure using calculate

[Gazi_Sohan]

Hi guys, I am trying to calculate Net Income between 2 accounts (account numbers between 400000 and 957090), however I want the account number to come from the columns instead of hard coded. I tried it and got the following exception.
How can I fix this ?

Here is the DAX measure that I am currently using

Net_income =
CALCULATE(
[Actual Amounts],
// COADetails_New[AccountID] >= "400000" && COADetails_New[AccountID] <= "957090"
COADetails_New[AccountID] >= 'Balance Sheet Table'[Start Account] && COADetails_New[AccountID] <= 'Balance Sheet Table'[End Account]
)

[Gazi_Sohan]

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 -
https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Need-Help-with-Calculate/m-p/3631102?lightbox-message-images-3631102=1023329i254D678F6871AC30#M140446 

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.

[Anonymous]

Hi @Gazi_Sohan,

Measure formula does not support to directly use other table columns across multiple tables.

I'l like to suggest you use aggregate functions to handle table field value to aggregate them and return get single value as condition in these expressions:

Net_income =
CALCULATE (
    [Actual Amounts],
    FILTER (
        ALLSELECTED ( COADetails_New ),
        COADetails_New[AccountID] >= MIN ( 'Balance Sheet Table'[Start Account] )
            && COADetails_New[AccountID] <= MAX ( 'Balance Sheet Table'[End Account] )
    )
)

Regards,

Xiaoxin sheng

[PijushRoy]

Hi @Gazi_Sohan 

What is the relationship you have between two tables, please share image for Data model
COADetails_New and 'Balance Sheet Table'

Is Account No, Start Account and End Account are number datatype or Text Datatype

Please reply

[Gazi_Sohan]

Hey @PijushRoy 
Please see my recent reply on this post. You will find answers to all of your questions there