General advice needed with CALCULATE(COUNTX)) measures

[Martin_Songstad]

Hi!

I have a fairly large fact_table, so I need to consider performance issues, while still allowing users to freely use slicers along several dimensions.

My measures look a bit like this - where 'Fact_table' [Event_X] is always 1 or 0.

Measure =

CALCULATE (

COUNTX (

Fact_table, [Event_X]

*IF ( complex_boolean_criterium_that_needs_to_be_checked_per_row_1 is true, 1)

*IF ( complex_boolean_criterium_that_needs_to_be_checked_per_row_2 is true, 1)

),

'Fact_table' [Event_X] = 1,

other_easy_critera = ..... ,

other_easy_critera = ..... ,

......,

)

Intuitively I would like to send as few rows as possible into the COUNTX-part by filtering on all possible (easy) criteria in the CALCULATE-part, but I am not sure this does the trick - does it? Should I put the easy criteria into the COUNTX-part as well as / instead of the CALCULATE-part to achieve this?

Any performance-enhancing advice appreciated 🤔

[amitchandak]

@Martin_Songstad , My advice would be to convert this <complex_boolean_criterium_that_needs_to_be_checked_per_row_1 > to the column, so that you can use filter and count and avoid the need to countX

Best at Source -> Followed by Power Query and DAX

[Martin_Songstad]

Thanks!

I managed to reformulate the complex criteria so that they fit in the CALCULATE-part, allowing me to COUNT rather than COUNTX, like this:

Measure =

CALCULATE (

COUNT ( Fact_table, [Event_X] ),

'Fact_table' [Event_X] = 1,

other_easy_critera = ..... ,

other_easy_critera = ..... ,

...... ,

complex_criterium_1 = ........(compares_values_across_multiple_tables)............ ,

complex_criterium_2 = ........(compares_values_across_multiple_tables)............

)

 It is still a bit slow. Does the order of the filter criteria matter? I would like to rule out as much as possible of the irrelevant data with the "easy" filter criteria, without also (redundantly) exposing it to those complex ones.