Forum Discussion
optimize this measure sumx
Hi,
Tell me how to optimize this measure?
I have 2 options, but both work for a long time and do not give a result when selecting all stores.
Option one
sumx(
FILTER(
SUMMARIZE('dbase sales3',
'dbase sales3'[Date],
'dbase sales3'[ID],
'dbase sales3'[Store],
'dbase sales3'[cashreg],
'dbase sales3'[#doc],
'dbase sales3'[#order],
"_unic", DISTINCTCOUNT('dbase sales3'[ID]),
"_count", sum('dbase sales3'[Count])),
'dbase sales3'[ID] <> "14763" &&
'dbase sales3'[#doc]<>0 &&
[_count]>=0 &&
'dbase sales3'[#order]=0),
[_unic])
Option two
VAR _Table =
SUMMARIZE('dbase sales3',
'dbase sales3'[Date],
'dbase sales3'[ID],
'dbase sales3'[Store],
'dbase sales3'[cashreg],
'dbase sales3'[#doc],
'dbase sales3'[#order],
"_unic", DISTINCTCOUNT('dbase sales3'[ID]),
"_count", sum('dbase sales3'[Count]))
Return sumx(FILTER(_Table,
'dbase sales3'[ID] <> "14763" &&
'dbase sales3'[#doc]<>0 &&
[_count]>=0 &&
'dbase sales3'[#order]=0),
[_unic])
Please help
Hi pani_victoria,
Thank you for reaching out to Microsoft Fabric Community.
Thank you rajendraongole1 for the prompt response
The previous version was grouping by Id, which caused an over expansion of the rows and incorrect results. Here is a revised version of the measure that will give the correct result, counting distinct Ids per combination, after applying your filters:
Optimized Measure :=
VAR FilteredTable =
FILTER(
'dbase sales3',
'dbase sales3'[ID] <> "14763" &&
'dbase sales3'[#doc] <> 0 &&
'dbase sales3'[Count] >= 0 &&
'dbase sales3'[#order] = 0
)VAR GroupedTable =
ADDCOLUMNS(
SUMMARIZE(
FilteredTable,
'dbase sales3'[Date],
'dbase sales3'[Store],
'dbase sales3'[cashreg],
'dbase sales3'[#doc],
'dbase sales3'[#order]
),
"_unic", CALCULATE(DISTINCTCOUNT('dbase sales3'[ID]))
)RETURN
SUMX(GroupedTable, [_unic])If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Thanks and regards,
Anjan Kumar Chippa
Hi pani_victoria - Thanks for the response, can you try the below:
OptimizedMeasure :=
VAR FilteredTable =
FILTER (
'dbase sales3',
'dbase sales3'[ID] <> "14763"
&& 'dbase sales3'[#doc] <> 0
&& 'dbase sales3'[Count] >= 0
&& 'dbase sales3'[#order] = 0
)VAR GroupedTable =
SUMMARIZE (
FilteredTable,
'dbase sales3'[Date],
'dbase sales3'[ID],
'dbase sales3'[Store],
'dbase sales3'[cashreg],
'dbase sales3'[#doc],
'dbase sales3'[#order]
)RETURN
COUNTROWS (GroupedTable)This works, as we are not calculating any per-row values anymore, just grouping the filtered rows and counting distinct combinations
Hope this works.
8 Replies
Hi pani_victoria - Avoid DISTINCTCOUNT on a grouped field (ID) inside SUMMARIZE — it’s redundant.Do not pre-calculate measures inside SUMMARIZE unless needed. Use ADDCOLUMNS if you must compute extra values.
Optimized Measure :=
VAR FilteredTable =
FILTER(
'dbase sales3',
'dbase sales3'[ID] <> "14763"
&& 'dbase sales3'[#doc] <> 0
&& 'dbase sales3'[Count] >= 0
&& 'dbase sales3'[#order] = 0
)VAR GroupedTable =
SUMMARIZE(
FilteredTable,
'dbase sales3'[Date],
'dbase sales3'[ID],
'dbase sales3'[Store],
'dbase sales3'[cashreg],
'dbase sales3'[#doc],
'dbase sales3'[#order],
"_unic", COUNTROWS(FilteredTable) -- Avoid DISTINCTCOUNT here since you're grouping by ID
)RETURN
SUMX(GroupedTable, [_unic])Hope this helps. please check and confirm
Your idea is very interesting, but when I use it in my report, I get the wrong result. For example, instead of 4 lines I get 30
Hi pani_victoria,
Thank you for reaching out to Microsoft Fabric Community.
Thank you rajendraongole1 for the prompt response
The previous version was grouping by Id, which caused an over expansion of the rows and incorrect results. Here is a revised version of the measure that will give the correct result, counting distinct Ids per combination, after applying your filters:
Optimized Measure :=
VAR FilteredTable =
FILTER(
'dbase sales3',
'dbase sales3'[ID] <> "14763" &&
'dbase sales3'[#doc] <> 0 &&
'dbase sales3'[Count] >= 0 &&
'dbase sales3'[#order] = 0
)VAR GroupedTable =
ADDCOLUMNS(
SUMMARIZE(
FilteredTable,
'dbase sales3'[Date],
'dbase sales3'[Store],
'dbase sales3'[cashreg],
'dbase sales3'[#doc],
'dbase sales3'[#order]
),
"_unic", CALCULATE(DISTINCTCOUNT('dbase sales3'[ID]))
)RETURN
SUMX(GroupedTable, [_unic])If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Thanks and regards,
Anjan Kumar Chippa
Hi pani_victoria - Thanks for the response, can you try the below:
OptimizedMeasure :=
VAR FilteredTable =
FILTER (
'dbase sales3',
'dbase sales3'[ID] <> "14763"
&& 'dbase sales3'[#doc] <> 0
&& 'dbase sales3'[Count] >= 0
&& 'dbase sales3'[#order] = 0
)VAR GroupedTable =
SUMMARIZE (
FilteredTable,
'dbase sales3'[Date],
'dbase sales3'[ID],
'dbase sales3'[Store],
'dbase sales3'[cashreg],
'dbase sales3'[#doc],
'dbase sales3'[#order]
)RETURN
COUNTROWS (GroupedTable)This works, as we are not calculating any per-row values anymore, just grouping the filtered rows and counting distinct combinations
Hope this works.
pani_victoria Hey,
try this measure for your requirement.OptimizedMeasure =
SUMX(
FILTER(
ADDCOLUMNS(
SUMMARIZE(
'dbase sales3',
'dbase sales3'[Date],
'dbase sales3'[ID],
'dbase sales3'[Store],
'dbase sales3'[cashreg],
'dbase sales3'[#doc],
'dbase sales3'[#order]
),
"_count", CALCULATE(SUM('dbase sales3'[Count]))
),
[ID] <> "14763" &&
[#doc] <> 0 &&
[#order] = 0 &&
[_count] >= 0
),
1 //
)Thanks
Harish M
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query
