Cant get correct total on complex model

[Mostafa-Hussien]

I have a complex data model imported from Oracle database, I am attaching a drawing of how it looks like.

I made a measure on a fact table "General Account" that works fine. It is basically like this

Measure1 = 
SUMX(
    FILTER(GENERAL_ACCOUNT,
    LEFT(GENERAL_ACCOUNT[ACCOUNTCODE],5) = "21031"),
    (GENERAL_ACCOUNT[DEBITVALUE] - GENERAL_ACCOUNT[CREDITVALUE])
)

The problem comes when I want to aggregate only positive values of this measure and display it in a table/matrix using dimension tables columns that have some active and some inactive relationships

The SUMX I use in my attempt to filter only positive values works on line values but not on total

Here is the first I tried without filtering positive values, which returns correct line and total values

Measure2 = 
CALCULATE(
    SUMX(
        GENERAL_ACCOUNT,
        [Measure1]
    ),
    ALLSELECTED(GENERAL_ACCOUNT[DEPTID], GENERAL_ACCOUNT[CLIENTID], GENERAL_ACCOUNT[DOCDATE], GENERAL_ACCOUNT[DOCNO]),
    --acc
    USERELATIONSHIP(DimLink[LinkCode],ACCOUNTS[Link]),
    USERELATIONSHIP(DimBU[BUCode], ACCOUNTS[BU]),
    --dep
    USERELATIONSHIP(DimLink[LinkCode], DEPARTMENTS[Link]),
    USERELATIONSHIP(DimBU[BUCode], DEPARTMENTS[BU]),
    --sector
    USERELATIONSHIP(DimLink[LinkCode], SECTORS[Link]),
    USERELATIONSHIP(DimBU[BUCode], SECTORS[BU]),
    --general acc
    USERELATIONSHIP(DimLink[LinkCode], GENERAL_ACCOUNT[Link]),
    USERELATIONSHIP(DimBU[BUCode], GENERAL_ACCOUNT[BU])
)

And here is my attempt for filtering on positive values which failed on total line

Measure3 = 
var int_talbe = 
    ADDCOLUMNS(
    SUMMARIZE(GENERAL_ACCOUNT,
    GENERAL_ACCOUNT[BU],
    GENERAL_ACCOUNT[Link],
    GENERAL_ACCOUNT[ACCOUNTCODE]),
    "value",
CALCULATE(
    SUMX(
        GENERAL_ACCOUNT,
        [Measure1]
    ),
    ALLSELECTED(GENERAL_ACCOUNT[DEPTID], GENERAL_ACCOUNT[CLIENTID], GENERAL_ACCOUNT[DOCDATE], GENERAL_ACCOUNT[DOCNO]),
    --acc
    USERELATIONSHIP(DimLink[LinkCode],ACCOUNTS[Link]),
    USERELATIONSHIP(DimBU[BUCode], ACCOUNTS[BU]),
    --dep
    USERELATIONSHIP(DimLink[LinkCode], DEPARTMENTS[Link]),
    USERELATIONSHIP(DimBU[BUCode], DEPARTMENTS[BU]),
    --sector
    USERELATIONSHIP(DimLink[LinkCode], SECTORS[Link]),
    USERELATIONSHIP(DimBU[BUCode], SECTORS[BU]),
    --general acc
    USERELATIONSHIP(DimLink[LinkCode], GENERAL_ACCOUNT[Link]),
    USERELATIONSHIP(DimBU[BUCode], GENERAL_ACCOUNT[BU])
)
    )
return
SUMX(
    int_table,
    IF([value]<0,0,[value])
)

Here is how both measures behave 

Any help?

[MohammadLoran25 · Accepted solution]

You'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]

Hi @MohammadLoran25 

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