optimize this measure sumx

[pani_victoria]

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

[v-achippa · Accepted solution]

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

[HarishKM]

@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

[rajendraongole1]

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

[rajendraongole1 · Accepted solution]

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]

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

[v-achippa · Accepted solution]

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

[v-achippa]

Hi @pani_victoria,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution I have provided for the issue worked? or let us know if you need any further assistance.
If my response addressed, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @pani_victoria,

We wanted to kindly follow up to check if the solution I have provided for the issue worked.
If my response addressed, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @pani_victoria,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution I have provided for the issue worked.
If my response addressed, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa