Forum Discussion
Helper INeed 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 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.
Helper IHey 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
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.