Forum Discussion

PhMeDie's avatar
PhMeDie
Helper I
6 years ago
Solved

Dax Formula Optimization - Counting transactions based on two facts

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

 

 

DAX
Measures
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
    Pragati11
    Super User
    6 years ago

    Hi PhMeDie ,

     

    Try following:

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

     

    Thanks,

    Pragati