Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
jmazzola31
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 =
ADDCOLUMNS (
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
some_bih
Super User
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)

https://www.sqlbi.com/articles/differences-between-groupby-and-summarize/ 

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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






jmazzola31
Frequent Visitor

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

 

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?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






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.

some_bih
Super User
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 #])

Did I answer your question? Kudos appreciated / accept solution!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.