Need Help with 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 
Let me simplify it for you. Forget about Net Income.

I have a created the DAX measures in which there is a filtering on Account ID column of COADetails table. 

Now the issue is that, in the measure, when I write the Account ID numbers as hardcoded, the measure works, but when I write those dynamically, it doesn't work. To avoid confusion, I have created the same DAX measure twice, one being hardcoded and the other being dynamic - 

I am providing the necessary files i.e. the datasource (excel file) and the Power BI report which you will get in the below link - 
https://drive.google.com/drive/folders/1xDiFgNOm_OpwcqfcgNhInF1homob_suP?usp=sharing

Can you please tell me what am I doing wrong ?

[Anonymous]

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. 

[Gazi_Sohan · Accepted solution]

Hey @Anonymous 
Thanks for your solution. Though it's working on the sample file but it didn't work out in our main file.

I have tried my own way by using a pretty simple approach i.e. using selectedvalue function and it's working perfect in the measure and made it dynamic - 

I have applied the same concept in the sample POC file that I provided for you (attached) - 

and it's also working fine in the sample file - 

But anyway, thanks for your help. I really appreciate it. You are a very talneted guy I must say.
Hats off 💥

.pbix file with my dynamic measure - 
https://drive.google.com/file/d/1jRhL9q_ytVP4OxSyjntFliLqdm_IXynP/view?usp=sharing

[Gazi_Sohan]

Hey @Anonymous 
Sorry for the late reply. I am having heavy loads in my office. Hope you understand !

We can't use any calculated column because that might increase the model size. The file that we are using currenlty is already huge in size and if we use calculated columns further it might increase the size and refresh time.

Your approach seems to be a reasonable one as far as I can see. Can you please avoid using the calculated columns and then follow the same approach and make it work ?

[Anonymous]

Hi @Gazi_Sohan ,

We can create measures.

Measure 1 = MID(MAX('COA Details'[Operator Between Totaling]), 1, SEARCH("..", MAX('COA Details'[Operator Between Totaling]), , 1) - 1) 

Measure 2 = MID(MAX('COA Details'[Operator Between Totaling]), SEARCH("..", MAX('COA Details'[Operator Between Totaling]), , 1) + 2, LEN(MAX('COA Details'[Operator Between Totaling])))

Dynamic Accounts = 
var selectedrecord = SELECTEDVALUE('COA Summary'[Account ID])
var _Description=SELECTEDVALUE('COA Summary'[Account Description])
var _a=CALCULATE([Measure 1],FILTER(ALL('COA Details'),'COA Details'[Account Description]=_Description))
var _b=CALCULATE([Measure 2],FILTER(ALL('COA Details'),'COA Details'[Account Description]=_Description))
var _c=CALCULATE([Measure 1],FILTER(ALL('COA Details'),'COA Details'[Account Description]=_Description))
var _d=CALCULATE([Measure 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. 

[Gazi_Sohan]

Hi @Anonymous ,
I have tested this out.

The surprising thing is that, this solution worked out for the sample file that I have provided, but it's not working in the main file. I exactly don't know the reason why. Let me figure out the reason why your solution is not working in the main file and let me get back to you soon.

[Anonymous]

Hi @Gazi_Sohan ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or if you are still confused about it, please feel free to let me know.

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. 

[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 - 

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 ,

How would you like to dynamically display the ID?
Is 400000 the minimum value of AccountID column and 957090 the maximum value of AccountID column?
Then you can use the

var _minid= MINX(ALL('COADetails_New'),[AccountID])
VAR _maxid= MAXX(ALL('COADetails_New'),[AccountID])
...
COADetails_New[AccountID] >= __minid && COADetails_New[AccountID] <= _maxid
...

Or you can create a slicer table. This allows you to manually select the id interval you want.

SlicerTable = VALUES(COADetails_New[AccountID])

var _minid= MINX(ALLSELECTED('SlicerTable'),[AccountID])
VAR _maxid= MAXX(ALLSELECTED('SlicerTable'),[AccountID])
...
COADetails_New[AccountID] >= __minid && COADetails_New[AccountID] <= _maxid
...

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. 

[Gazi_Sohan]

Hey @Anonymous ,
Answer to your questions - 
1. "Is 400000 the minimum value of AccountID column and 957090 the maximum value of AccountID column?" 
Ans: No, this is a range of Account ID's particularly for the category "Net Income" and those range of AccountIDs exist under the AccountID column of COADetails_New table.

Net Income is a summation of all the AccountID's ranging from 400000 to 957090 from COADetails_New[AccountID] column. Now, we have tried to show this by using the DAX measure that I have already shared before which takes the numbers 400000 and 957090 as hard-coded, not dynamically. These Account IDs are present with COADetails_New table.

The word "Net Income" is not present in the COADetails_New table, but from the client's info, we know that AccountIDs raniging between 400000 and 957090 are all part of net income and the summation of the values under all these AccountIDs will give us the value of Net Income.

These accountIDs (400000-957090) are also present in Fact table and as Fact table and COADetails_New table are connected so these range of accounts are pulling the corresponding values from Fact table and then summing up and finally we are getting Net_Income. This is how the DAX measure is working.

The slicer table didn't work because our client don't want any slicer table and then choose. Thanks for keeping me updated.

Gazi

[Anonymous]

Hi @Gazi_Sohan ,

According to your description, Net Income only exists in the Balance table? Then you need to import the Balance table. Then modify the measure as shown below:

...
var _table=CALCULATETABLE(VALUES('Balance'[AccountID]),'Balance'[ASSETS]="net income")
......
return switch(....
.....
COADetails_New[AccountID] in _table
...

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

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. 

[tamerj1]

Hi @Gazi_Sohan 

please try

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