Forum Discussion
Helper ICant get correct total on complex model
You're welcome,
Yes I tried it in the file you sent and it worked.
No worries.
Helper IThanks
I will try it in the next few hours.
However, did you try it?
I have bad experience using SUMMARIZECOLUMNS inside a measure. It always return an error of the sort "ADDMISSINGITEMS cannot bu used in this context" even though the same syntax work fine if I use it in a calculated table
So did it work fine with you without errors?
Solution SageYou're welcome,
Yes I tried it in the file you sent and it worked.
No worries.
- Mostafa-Hussien
MohammadLoran25 Thank you !
This actually worked after days of struggle, I really appreciate your help !
Can I bother you with more questions ?
This measure is a part of 8 to produce one measure (this is d3 and there is d1 to d8)
I had the first problem of aggregating the positive values correctly, thanks to you I will do the same for the other 7 measures (each one uses a other tables than Genral_Account)
My second concern was the processing. In the beginning I was able to aggregate the positive values using a measure that uses GENERATE on SECTORS and ACCOUNTS, then another measure for the line values using GENERATE on ACCOUNTS then SECTORS, then a third measure that uses ISINSCOPE to determine which measure to use at which aggreagtion level.
When I published this into PBI Service, I recieved an exceeding processing memory error
So I decided to let go of the many GENERATEs and try something less heavy
I will start attempting to use your solution on the remaining 7 and see what shall I recieve
But I am asking upfront, do you have ideas on how to decrease its processing footprint?
I was thinking in Measure2 for example to use VALUES(GENERAL_ACCOUT[ACCOUNTS]) instead of the entire GENERAL_ACCOUNT in the SUMX ?
Again, thank you !
- MohammadLoran25
You're welcome Mostafa-Hussien
Actually SUMX is an iterator which based on the number of records can cause memory issue.
Besides that, ADDCOLUMNS could make it slower as well. So we need to be carefull in using both ADDCOLUMNS and SUMX together.
You can do lots of things to optimize both your data model and dax measures as well.
And of course yess,if you use VALUES() instead of the entire table for SUMX, it would be faster.
Hope This helps you. Btw, It needs reevaluation to be fixed.
Regards,
Loran
- Mostafa-Hussien
I have applied your concept on all my measures and they worked, except for one last measure that had an extra granularity.
I have tried many trials to cascade the values correctly but it doesnt work so far
This time its another table and should be also grouped by Currency which was not required in all the past measures. I also changed the model a little bit to save processing time.
And there is an extra requirement; the values to be displayed only on a certain Sector (if Main = 1)
I attached the model if you can help ?
For the selected BU and Link, Measure 1 calculates the values correctly and applies the same values throughout all Sectors. When I try to define a certain Sector for value displaying the totals get wrong
https://www.dropbox.com/s/mpds1c2cwcbrs2x/box-test.pbix?dl=0
Many thanks in advance