Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

Cant get correct total on complex model

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

 

MostafaHussien_1-1680313557048.png

 

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 

MostafaHussien_0-1680313538955.png

 

Any help?

1 ACCEPTED SOLUTION

You're welcome,

Yes I tried it in the file you sent and it worked.

No worries.

View solution in original post

24 REPLIES 24
tamerj1
Super User
Super User

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 )
)

Hi @tamerj1 

Thanks for your reponse

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

Any thing else in mind ?

@Mostafa-Hussien 

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],

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 )
)

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 ?

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

MohammadLoran25
Super User
Super User

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

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

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] )

 

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 ?

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

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

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

 

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

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

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] )

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

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.

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)

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

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

 

Thanks

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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors