Dax Formula Optimization - Counting transactions based on two facts

[PhMeDie]

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 )
    )

[Anonymous · Accepted 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

[Pragati11]

Hi @PhMeDie ,

Try following:

Transactions = CALCULATE (
            DISTINCTCOUNTNOBLANK ( Sales[Transaction key] ),
        FILTER ( Sales, Footfall[Footfall] > 0 )
    )

Thanks,

Pragati