cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

Accounts Sold Measure Performance

Hello All,

I am trying to calculate a measure from the main Sales Table in my report. The Sales Table is the fact table and the model follows a star schema. The Sales Table contains 4.7 million rows.

The measure I am trying to calculate is an  "Account Sold Measure". The purpose of this measure is to see the numbers of customers that purchased anything > 0 in the current filter context. For example, if brand A is displayed in a Table Visual and the filtered time period is YTD I want the metric to show the number of accounts that had > 0 purchases year to date for brand A. Listed below is the measure I am currently using to find this number.

Accounts Sold Measure =
VAR Account_Sold_Table =
SUMMARIZE ( 'Sales Table', 'Sales Table'[Account #] ),
"Accounts Sold Summary", IF ( ROUND ( CALCULATE ( SUM ( 'Sales Table'[Cases Column] ) ), 2 ) > 0, 1, 0 )
)

RETURN
SUMX ( Account_Sold_Table, [Accounts Sold Summary] )

This measure works but it is slow. I am wondering if there is anything I can do to clean up the dax and make it perform faster.

Any help is appreciated. Let me know if more information is needed.

5 REPLIES 5
Super User

Hi @jmazzola31 optimization is not easy, based what you wrote please try following measure as test  and if this is not ok for you please check article on link for optimization (apply on your case)

Count Account Measure=
CALCULATE(
DISTINCTCOUNT('Sales Table'[Account #]),
'Sales Table'[Cases Column]>0
)

Proud to be a Super User!

Frequent Visitor

how would I account for the fact I need a table with accounts that have > 0 cases sold?

Super User

Hi @jmazzola31 assumption for my proposed solution is fact that in your fact table there are already ONLY sold items (customers/accounts key column) sold AND amount of sales is always >0.

I hope this is in your case?

Proud to be a Super User!

Frequent Visitor

the fact table holds orders of all types. The orders can be 0, negative or positive.

That is why I think I need to create a summarize variable to act is a sub query on the account number and then sum all cases on the account and then check to see if those net cases are greater than 0 or not.

Super User

Hi @jmazzola31 possible solution to count number of customer(if account in Sales Table is customer id) is measure as following

Count Account: DISTINCTCOUNT('Sales Table'[Account #])

Proud to be a Super User!