topic Re: Cant get correct total on complex model in DAX Commands and Tips

Cant get correct total on complex model

Mostafa-Hussien — Sat, 01 Apr 2023 01:45:49 GMT

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?

Re: Cant get correct total on complex model

MohammadLoran25 — Sat, 01 Apr 2023 07:29:53 GMT

Hi Mostafa-Hussien ,

I think it is because of the ALLSELECTED function that you have in Measure3.

Please comment it and let me know the result.

Regards,

Loran

Re: Cant get correct total on complex model

tamerj1 — Sat, 01 Apr 2023 08:35:20 GMT

Hi Mostafa-Hussien

please try

Measure3 =
VAR SelectedDims =
ALLSELECTED (
GENERAL_ACCOUNT[DEPTID],
GENERAL_ACCOUNT[CLIENTID],
GENERAL_ACCOUNT[DOCDATE],
GENERAL_ACCOUNT[DOCNO]
)
RETURN
SUMX (
SUMMARIZE (
GENERAL_ACCOUNT,
GENERAL_ACCOUNT[BU],
GENERAL_ACCOUNT[Link],
GENERAL_ACCOUNT[ACCOUNTCODE]
),
VAR Value1 =
CALCULATE (
SUMX ( GENERAL_ACCOUNT, [Measure1] ),
SelectedDims,
--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
IF ( Value < 0, 0, Value1 )
)

Re: Cant get correct total on complex model

Mostafa-Hussien — Sat, 01 Apr 2023 20:14:54 GMT

Hi tamerj1

Thanks for your reponse

Unfortunately it gives the same correct line value and 0 on total

Any thing else in mind ?

Re: Cant get correct total on complex model

Mostafa-Hussien — Sat, 01 Apr 2023 20:16:24 GMT

I cannot drop the ALLSELETED(), otherwise it will not aggregate regardless of the ALLSELECTED arguments.

I mean; I added certain columns to ALLSELECTED like CLIENTID, because I want an aggregate value regardless of CLIENTID

Re: Cant get correct total on complex model

MohammadLoran25 — Sat, 01 Apr 2023 20:26:40 GMT

Not the best solution, but as an easy and fast solution to fix it, please try this as well. It should work for your total:

Measure = VAR _TABLEE = ADDCOLUMNS ( SUMMARIZE ( GENERAL_ACCOUNT, GENERAL_ACCOUNT[BU], GENERAL_ACCOUNT[Link], GENERAL_ACCOUNT[ACCOUNTCODE] ), "@measure3", [measure3] ) RETURN SUMX ( _TABLEE, [@measure3] )

Re: Cant get correct total on complex model

Mostafa-Hussien — Sat, 01 Apr 2023 20:32:23 GMT

Thanks MohammadLoran25

This works fine for returning positive and negative values and correct total

But how can i retrieve only positive values while retaining a correct total ?

Re: Cant get correct total on complex model

tamerj1 — Sat, 01 Apr 2023 20:36:44 GMT

Mostafa-Hussien

Try

GENERAL_ACCOUNT[SECTORID]
),
VAR Value1 =
CALCULATE (
SUMX ( GENERAL_ACCOUNT, [Measure1] ),
SelectedDims,
--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
IF ( Value < 0, 0, Value1 )
)

Re: Cant get correct total on complex model

MohammadLoran25 — Sat, 01 Apr 2023 20:42:10 GMT

Your [measure3] only returns postivie value so the measure I sent recently should work for positive values.

Re: Cant get correct total on complex model

Mostafa-Hussien — Sat, 01 Apr 2023 20:44:07 GMT

SECTORID is not a column on the GENERAL_ACCOUNT table. GENERAL_ACCOUNT table has a DEPTID column that has an active relationship many to one on SECTOR table.

So instead I tried adding GENERAL_ACCOUNT[DEPTID] on the SUMMARIZE, and this time it returned a higher value for the positive value, and unfortunately the total is still 0

I appreciate your response a lot. Any other methods to try ?

Re: Cant get correct total on complex model

Mostafa-Hussien — Sat, 01 Apr 2023 20:49:51 GMT

Thanks MohammadLoran25

Oh sorry, I corrected my measure according to what you said.

Now it returns the correct positive line value but still the total is zero

Re: Cant get correct total on complex model

MohammadLoran25 — Sat, 01 Apr 2023 20:55:23 GMT

In SUMX, you put [@measure3] or [measure3] ?

[@measure3] is the name we gave to it in table VAR. You put this [@measure3]?

Re: Cant get correct total on complex model

Mostafa-Hussien — Sat, 01 Apr 2023 20:57:49 GMT

Yes, I double-checked and yes. And it still has 0 on total

Re: Cant get correct total on complex model

MohammadLoran25 — Sat, 01 Apr 2023 21:25:50 GMT

How about this one:

Measure = VAR _TABLEE = ADDCOLUMNS ( SUMMARIZE ( GENERAL_ACCOUNT, GENERAL_ACCOUNT[BU], GENERAL_ACCOUNT[Link], GENERAL_ACCOUNT[ACCOUNTCODE] ), "@MYMEASURE", IF([measure2]>0,[measure2]) ) RETURN SUMX ( _TABLEE, [@MYMEASURE] )

Re: Cant get correct total on complex model

Mostafa-Hussien — Sat, 01 Apr 2023 21:33:43 GMT

Still correct positive line values and blank on total and negative values

Re: Cant get correct total on complex model

MohammadLoran25 — Sat, 01 Apr 2023 22:41:09 GMT

And this one:

Measure = VAR _TABLEE = ADDCOLUMNS ( SUMMARIZE ( GENERAL_ACCOUNT, GENERAL_ACCOUNT[BU], GENERAL_ACCOUNT[Link], GENERAL_ACCOUNT[ACCOUNTCODE], GENERAL_ACCOUNT[DeptId] ), "@MYMEASURE", IF([measure2]>0,[measure2]) ) RETURN SUMX ( _TABLEE, [@MYMEASURE] )

If not, please share a sample of your data and data model.

Re: Cant get correct total on complex model

Mostafa-Hussien — Sat, 01 Apr 2023 22:54:08 GMT

This tripled the positive value while negative and total are still blank

Yes, I will prepare a .pbix file with required tables (its a big model with 35+ tables in snowflake, so I wil cut it only to tables relative to this problem)

Re: Cant get correct total on complex model

Mostafa-Hussien — Sat, 01 Apr 2023 23:25:50 GMT

I uploaded the pbix file here

https://www.dropbox.com/s/uxr3htuaxrz9hk8/d3-test.pbix?dl=0

This contains the relevant tables and my original measures and the result that I am not able to alter. I really appreciate if you can help me with this riddle

Re: Cant get correct total on complex model

Mostafa-Hussien — Sat, 01 Apr 2023 23:26:16 GMT

I uploaded the pbix file here

https://www.dropbox.com/s/uxr3htuaxrz9hk8/d3-test.pbix?dl=0

This contains the relevant tables and my original measures and the result that I am not able to alter. I really appreciate if you can help me with this riddle

Re: Cant get correct total on complex model

MohammadLoran25 — Sun, 02 Apr 2023 09:09:46 GMT

Hi Again Mostafa-Hussien ,
This is what you need:

FinalMeasure = VAR _TABLEE = ADDCOLUMNS ( SUMMARIZECOLUMNS ( 'Fact_General_Account'[BU], 'Fact_General_Account'[Link], 'DimSectors'[SECTORID], 'Fact_General_Account'[ACCOUNTS.ACCOUNTCODE], DimDept[SECTORID] ), "@MYMEASURE", [measure3] ) RETURN SUMX ( _TABLEE, [@MYMEASURE] )

Regards,

Loran