Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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.
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
)
Proud to be a Super User!
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?
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.
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!
Proud to be a Super User!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
72 | |
62 | |
51 | |
48 |
User | Count |
---|---|
204 | |
86 | |
62 | |
59 | |
56 |