topic Re: This visual has exceeded the available resources in DAX Commands and Tips

This visual has exceeded the available resources

Anonymous — Tue, 05 May 2020 09:05:27 GMT

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

Re: This visual has exceeded the available resources

Greg_Deckler — Tue, 05 May 2020 11:36:06 GMT

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

Re: This visual has exceeded the available resources

Anonymous — Tue, 05 May 2020 11:51:13 GMT

okay , thanks

Re: This visual has exceeded the available resources

Anonymous — Tue, 05 May 2020 12:18:33 GMT

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

Re: This visual has exceeded the available resources

Anonymous — Tue, 05 May 2020 12:17:02 GMT

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