topic Filter SUMMARIZECOLUMNS based on separate measure in DAX Commands and Tips

Filter SUMMARIZECOLUMNS based on separate measure

Anonymous — Fri, 24 Apr 2020 16:53:51 GMT

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.

Re: Filter SUMMARIZECOLUMNS based on separate measure

Stachu — Fri, 24 Apr 2020 19:01:10 GMT

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

Re: Filter SUMMARIZECOLUMNS based on separate measure

Anonymous — Fri, 24 Apr 2020 22:22:19 GMT

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