Filter SUMMARIZECOLUMNS based on separate measure

[brentmav]

I'm hoping to get some guidance of improving a Report Builder dataset query to reduce the number of rows returned.  At the moment, I'm returning close to 40,000 rows, but ultimately only 18,000 rows would be included in the final report.

Dataset is a star schema with 4 dimension tables and 1 fact table.

Generically, the query DAX is as follows:

EVALUATE
SUMMARIZECOLUMNS (
'DIM_TABLE_1'[CATEGORY_A],
'DIM_TABLE_2'[CATEGORY_B],
'DIM_TABLE_3'[CATEGORY_C],
'DIM_TABLE_4'[CATEGORY_D],
FILTERCONDITION_1,
FILTERCONDITION_2,
FACT_MEASURE_1,
FACT_MEASURE_2
)

However, there's a combination of the first 3 categories (from 3 separate dimension tables) that needs to be filtered out based on a separate measure (call it FACT_MEASURE_3 > 0).

I imagine I could include that measure in the original DAX query, but I'm not clear how to construct that filter condition and how it fits into the query.  

Any help with the structure of the query or related resources would be greatly appreciated.

[Anonymous]

Depending on the semantics of your query... you could try the following:

EVALUATE
CALCULATETABLE(
	SUMMARIZECOLUMNS (
		'DIM_TABLE_1'[CATEGORY_A],
		'DIM_TABLE_2'[CATEGORY_B],
		'DIM_TABLE_3'[CATEGORY_C],
		'DIM_TABLE_4'[CATEGORY_D],
		FILTERCONDITION_1,
		FILTERCONDITION_2,
		FACT_MEASURE_1,
		FACT_MEASURE_2
	),
	FILTER(
		SUMMARIZE(
			FactTable,
			'DIM_TABLE_1'[CATEGORY_A],
			'DIM_TABLE_2'[CATEGORY_B],
			'DIM_TABLE_3'[CATEGORY_C]	
		),
		[FACT_MEASURE_3] > 0
	)
)

// 2. version, could be faster

CALCULATETABLE(
	SUMMARIZECOLUMNS (
		'DIM_TABLE_1'[CATEGORY_A],
		'DIM_TABLE_2'[CATEGORY_B],
		'DIM_TABLE_3'[CATEGORY_C],
		'DIM_TABLE_4'[CATEGORY_D],
		FILTERCONDITION_1,
		FILTERCONDITION_2,
		FACT_MEASURE_1,
		FACT_MEASURE_2
	),
	FILTER(
		CROSSJOIN(
			VALUES( 'DIM_TABLE_1'[CATEGORY_A] ),
			values( 'DIM_TABLE_2'[CATEGORY_B] ),
			VALUES( 'DIM_TABLE_3'[CATEGORY_C] )
		),
		[FACT_MEASURE_3] > 0
	)
)

Best

D

[Stachu]

you can do something like this:

EVALUATE
FILTER(
SUMMARIZECOLUMNS (
'DIM_TABLE_1'[CATEGORY_A],
'DIM_TABLE_2'[CATEGORY_B],
'DIM_TABLE_3'[CATEGORY_C],
'DIM_TABLE_4'[CATEGORY_D],
FILTERCONDITION_1,
FILTERCONDITION_2,
FACT_MEASURE_1,
FACT_MEASURE_2,
FACT_MEASURE_3,
)
FACT_MEASURE_3>0
)

would that work for you? 

If you add sample tables (in format that can be copied to PowerBI) from your model with anonymised data, I could be more specific. Like this (just copy and paste into the post window).

Column1	Column2
A	1
B	2.5