Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
a quick question to the Pros.
I need the number of transactions for all days on which we had recorded visitor footfall in our stores. Information is stored in two tables - Sales and Footfall. The two facts are linked through two common tables (Store and Calendar).
Do you see any way to speed up this measure?
Counting on your help!
Cheers.
Transactions = CALCULATE (
CALCULATE (
DISTINCTCOUNTNOBLANK ( Sales[Transaction key] )
),
FILTER ( Sales, Footfall[Footfall] > 0 )
)
Solved! Go to Solution.
The golden rule of DAX says: "You should NEVER filter a table when you can filter a column." One of the reasons is... speed. You are filtering the full expanded(!!!) table Sales and put it all as a filter. This is one of the worst things you can do in DAX. Instead, you should always filter columns only.
// Assumption is that Store and Calendar
// are dimensions connected to 2 fact
// tables, Sales and Footfall, and the
// connection in 1:* and the filtering
// is one-way.
Transactions =
var __DaysAndStoresWithFootfall =
CALCULATETABLE(
SUMMARIZE(
Footfall,
Store[StoreID],
Calendar[Date]
),
// No columns from fact tables
// should ever be exposed to
// the end user. If you do
// expose them (very bad),
// you have to wrap this condition
// in KEEPFILTERS.
Footfall[Footfall] > 0
)
var __result =
CALCULATE (
// Why not DISTINCTCOUNT?
DISTINCTCOUNTNOBLANK ( Sales[Transaction key] ),
__DaysAndStoresWithFootFall,
ALL( Stores ),
ALL( 'Calendar' )
)
return
__result
The golden rule of DAX says: "You should NEVER filter a table when you can filter a column." One of the reasons is... speed. You are filtering the full expanded(!!!) table Sales and put it all as a filter. This is one of the worst things you can do in DAX. Instead, you should always filter columns only.
// Assumption is that Store and Calendar
// are dimensions connected to 2 fact
// tables, Sales and Footfall, and the
// connection in 1:* and the filtering
// is one-way.
Transactions =
var __DaysAndStoresWithFootfall =
CALCULATETABLE(
SUMMARIZE(
Footfall,
Store[StoreID],
Calendar[Date]
),
// No columns from fact tables
// should ever be exposed to
// the end user. If you do
// expose them (very bad),
// you have to wrap this condition
// in KEEPFILTERS.
Footfall[Footfall] > 0
)
var __result =
CALCULATE (
// Why not DISTINCTCOUNT?
DISTINCTCOUNTNOBLANK ( Sales[Transaction key] ),
__DaysAndStoresWithFootFall,
ALL( Stores ),
ALL( 'Calendar' )
)
return
__result
Hi @PhMeDie ,
Try following:
Transactions = CALCULATE (
DISTINCTCOUNTNOBLANK ( Sales[Transaction key] ),
FILTER ( Sales, Footfall[Footfall] > 0 )
)
Thanks,
Pragati
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |