Summarize with filter

[Anonymous]

Hi!

Hope somebody can help me with this.

I've summarized a table from a master table where I have monthly sales by IdPais, IdZonaGestion, and other dimensions. The current code for the summarized table is:

Tabla Devaluación = 
SUMMARIZE(
	'Ventas Mensuales';
	'Ventas Mensuales'[IdPais];
	'Ventas Mensuales'[IdZonaGestion];
	"ML";
	CALCULATE(
		SUM(
			'Ventas Mensuales'[ML]
		);
		ALLEXCEPT(
			'Ventas Mensuales';
			'Ventas Mensuales'[IdPais]
		);
		USERELATIONSHIP(
			'Ventas Mensuales'[Fecha];
			'Período'[Fecha]
		)
	)
)

This works pretty good for me, since the final objective is to have sales by IdPais with its respective IdZonaGestion. So, in this scenario, I would have the same value for each country no matter what IdZonaGestion it has.

The only problem is that using the function ALLEXCEPT also excludes other filters that I would want to keep. I have report filters that I want to keep and I also want it to be dynamic depending on the selection of the period.

How should I change this code to perform as described? Should I use a other function and not ALLEXCEPT?

Here some pics of model and report:

Thanks in advance!

[v-ljerr-msft · Accepted solution]

Hi @Anonymous,

According to your description above, I don't think your problem is with using ALLEXCEPT function which excludes other filters. It is that the Calculate table is evaluated during the database processing(like a data refresh) and then stored in the model, and it cannot be affected by user selection or other filters on the report.

In this scenario, I would suggest you to create a measure to calculate "ML", then show the measure on a Table visual with "IdPais", and "IdZonaGestion" column on the report. 

ML =
CALCULATE (
    SUM ( 'Ventas Mensuales'[ML] );
    ALLEXCEPT ( 'Ventas Mensuales'; 'Ventas Mensuales'[IdPais] );
    USERELATIONSHIP ( 'Ventas Mensuales'[Fecha]; 'Período'[Fecha] )
)

Regards

[Anonymous]

Hi @v-ljerr-msft. Thanks for your contribution!

Your specific example, helped me to work it out.

Thanks!

[Phil_Seamark]

This is an old but very useful article explaining the difference in usefulness by each of the filter hints

http://sqlblog.com/blogs/marco_russo/archive/2010/04/05/all-allexcept-and-values-in-dax.aspx

[Anonymous]

Hi @Phil_Seamark, thanks for your reply.

I read the article. Sadly, I have to say that it didn't help me much. I tried what's mentioned in the article but still not getting what I need. I probably need a more detailed help using my specific scenario.

Thanks for your help.