This visual has exceeded the available resources

[Anonymous]

this mesure in my table causes a problem , the visual of the table doesn't appear 

I want to optimize this DAX formula : 

%marque_prev = var vCoutprev_marge = CALCULATE(

SUMX(DISTINCT('polaris_datamart fait_activite'[cout_theorique]);

'polaris_datamart fait_activite'[cout_theorique])

; FILTER('polaris_datamart fait_activite';

RELATED('polaris_datamart dim_intervenant'[type_intervenant]) = "collaborateur")

) * SUMX( FILTER('polaris_datamart fait_activite';

RELATED('polaris_datamart dim_nature'[label_nature]) = "Projet" &&

RELATED('polaris_datamart dim_gratuite'[gratuite]) = FALSE() &&

RELATED('polaris_datamart dim_intervenant'[type_intervenant]) = "collaborateur");

'polaris_datamart fait_activite'[nb_jour_realise] )

var marge = IF( NOT(ISBLANK([CA_CA+CRA])) &&

NOT(ISBLANK([%marge_CA+CRA])) &&

[CA_CA+CRA]<>0 ;

INT("1") - DIVIDE(vCoutprev_marge;[CA_CA+CRA]);

INT("1")

)

return marge

[Anonymous]

Try this. I can't guarantee it's going to be a lot better (but is certainly better) because I don't know the model.

[%marque_prev] =
// Please do not use cryptic names that nobody
// understands. Consider others as well,
// not only yourself. Many thanks.
var __another_badly_named_measure = [%marge_CA+CRA]
return
	IF( 
		ISBLANK( __another_badly_named_measure ),
		1,
		
		// if not blank...
		var __measure1 = 
			CALCULATE(
				SUMX(
					DISTINCT('polaris_datamart fait_activite'[cout_theorique]),
					'polaris_datamart fait_activite'[cout_theorique]
				),
				KEEPFILTERS(
					'polaris_datamart dim_intervenant'[type_intervenant] = "collaborateur"
				)
			)
		var measure2 =
			CALCULATE(
				SUM( 'polaris_datamart fait_activite'[nb_jour_realise] ),
				KEEPFILTERS(
					'polaris_datamart dim_nature'[label_nature] = "Projet"
				),
				KEEPFILTERS(
					NOT( 'polaris_datamart dim_gratuite'[gratuite] )
				),
				KEEPFILTERS(
					'polaris_datamart dim_intervenant'[type_intervenant] = "collaborateur"
				)
			)
		var vCoutprev_marge = __measure1 * __measure2
		var __ca_ca_cra = [CA_CA+CRA]
		return
			1 - DIVIDE( vCoutprev_marge, __ca_ca_cra )
	)

Yes, the logic should return exactly the same result as your original measure.

Best
D

[Anonymous]

Another version...

[%marque_prev] =
var __another_badly_named_measure = [%marge_CA+CRA]
return
	switch( true(),
		
		ISBLANK( __another_badly_named_measure ), 1,
		
		var __ca_ca_cra = [CA_CA+CRA]
		return
			if( __ca_ca_cra = 0,
				1,
				
				var __measure1 = 
					CALCULATE(
						SUMX(
							DISTINCT('polaris_datamart fait_activite'[cout_theorique]),
							'polaris_datamart fait_activite'[cout_theorique]
						),
						KEEPFILTERS(
							'polaris_datamart dim_intervenant'[type_intervenant] = "collaborateur"
						)
					)
				var measure2 =
					CALCULATE(
						SUM( 'polaris_datamart fait_activite'[nb_jour_realise] ),
						KEEPFILTERS(
							'polaris_datamart dim_nature'[label_nature]) = "Projet"
						),
						KEEPFILTERS(
							NOT( 'polaris_datamart dim_gratuite'[gratuite] )
						),
						KEEPFILTERS(
							'polaris_datamart dim_intervenant'[type_intervenant] = "collaborateur"
						)
				var vCoutprev_marge = __measure1 * __measure2
				return
					1 - DIVIDE( vCoutprev_marge, __ca_ca_cra )
			)
	)

Best

D

[Greg_Deckler]

Very difficult to troubleshoot without data, etc. See my 2 part series on troubleshooting DAX performance issues:

https://community.powerbi.com/t5/Community-Blog/Performance-Tuning-DAX-Part-1/ba-p/976275

https://community.powerbi.com/t5/Community-Blog/Performance-Tuning-DAX-Part-2/ba-p/976813

[Anonymous]

okay , thanks