optimize this measure sumx

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.