Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to Solution.
You're welcome,
Yes I tried it in the file you sent and it worked.
No worries.
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 ?
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
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?
User | Count |
---|---|
41 | |
27 | |
23 | |
19 | |
16 |
User | Count |
---|---|
54 | |
35 | |
19 | |
18 | |
15 |