Forum Discussion
Need Help with Calculate
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
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 ?
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.
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=sharingHey 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 ?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.
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.