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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.