topic Re: Need help with Dax Measure in DAX Commands and Tips

Need help with Dax Measure

Anonymous — Thu, 02 Nov 2023 11:36:07 GMT

Hi,
I have facing performance issue with a measure in my power bi report where I have creating the below measure

Transaction Count =
COUNTROWS (
FILTER (
SUMMARIZE (
'Compiled Sales',
'Compiled Sales'[Store Code],
'Compiled Sales'[BillNo],
"Total", SUM ( 'Compiled Sales'[Sold Quantity] )
),
[Total] > 0
)
)

can this measure be created in another way currently this dax is taking 20 sec and my main fact table has 20 million records.
please let me know if any other information is requried.

Re: Need help with Dax Measure

tamerj1 — Thu, 02 Nov 2023 12:45:50 GMT

Hi Anonymous
Usually it should not be slow but in some occasions when creating a column inside SUMMARIZE. The thing is this is a simple sum calculation which leaves in doubts. However, you may try the following to avoid creating the column inside SUMMARIZE.

Transaction Count = SUMX ( SUMMARIZE ( 'Compiled Sales', 'Compiled Sales'[Store Code], 'Compiled Sales'[BillNo] ), INT ( CALCULATE ( SUM ( 'Compiled Sales'[Sold Quantity] ) ) > 0 ) )