Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Summarize with filter

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:

Sin título.pngSin título2.pngSin título3.png

 

Thanks in advance!

1 ACCEPTED SOLUTION
v-ljerr-msft
Employee
Employee

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. Smiley Happy

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

 

Regards

View solution in original post

4 REPLIES 4
v-ljerr-msft
Employee
Employee

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. Smiley Happy

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

 

Regards

Anonymous
Not applicable

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

 

Your specific example, helped me to work it out.

 

Thanks!

Phil_Seamark
Employee
Employee

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

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Anonymous
Not applicable

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.