Combine ALL() and FILTER() to remove rows

[Anonymous]

Dear all, 

I am relatively new to PowerBI and I am having some trouble understanding what criteria uses PowerBI to display or hide rows. 

Let's assume the following dummy data about sales:

CountryTable:

country_id	country
1	Spain
2	Netherlands
3	Germany

SalesTable:

sale_id	country_id	province	amount_euros
1	2	A	50
2	2	B	100
3	3	C	20

Both this tables are related by the country field with "direction" set to "Both". 

1. If I create a matrix with:

Rows: CountryTable.country

Values: SalesTable.amount_euros

Then, in the result table, PowerBI is omitting Spain, since it doesn't have any sales.

Now I want to do the same with the following calculation and I cannot understand why in this case PowerBI is displaying Spain. I want it to not show up:

2. I want to create a measure that displays the sum of amount_euros for provinces A and C, (but not filtered by country, so using the ALL() function). In other words, I want the following table:

country	new_measure
Netherlands	70
Germany	70

If I do:

new_measure = 

VAR _table = FILTER (

ALL (SalesTable),

SalesTable[province] = "A" || SalesTable[province] = "C"

)

RETURN SUMX ( _table, [amount_euros] ) 

PowerBI is displaying:

country	new_measure
Netherlands	70
Germany	70
Spain	70

Isn't the filter of province supposed to "propagate"? Spain is not even showing up in the sales so Province is neither A or C...

Thank you very much in advance

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

Hello @Ashish_Mathur 

Thank you for your response. I am afraid though this is not the result I need: I need 70 in all rows, as I was stating in my original post.

Regards,

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[selimovd]

Hello @Anonymous ,

in DAX every calculation is done independent of the calculations of other fields.

This means when you create in the visualization a table from the column CountryTable[country], the table looks like this:

country

Spain

Netherlands

Germany

If you add now the sum of SalesTable[amount_euros], this will be calculated for each row:

country	Formula evaluated	What is displayed
Spain	SUM(amount_euros)	blank ( will be hidden because blank)
Netherlands	SUM(amount_euros)	150
Germany	SUM(amount_euros)	20

What happens now when you use the FILTER function?

The same mechanism happens for your calculation.

The FILTER(ALL(SalesTable))... will produce a table that looks like that:

sale_id	country_id	province	amount_euros
1	2	A	50
3	3	C	20

I will call this table for the rest of my response 'FILTER-Table'.

On this 'FILTER-Table' you use the SUMX function. Important here is that SUMX has a filter context (all the filter in the context, like the country in each row) and a row context (what happens as second argument, in your case you just refer to the column SalesTable[amount_euros] (by the way, always write column including the table and measures without. This is the common way to write DAX)).

So your SUMX is using 'FILTER-Table' and doing a SUM of the column 'FILTER-Table'[amount_euros], what is 50+20 = 70.

Your question is now why is Spain showing up?

This is what happens:

country	sum	What is displayed
Spain	The FILTER function creates 'FILTER-Table', then SUMX happens	70
Netherlands	The FILTER function creates 'FILTER-Table', then SUMX happens	70
Germany	The FILTER function creates 'FILTER-Table', then SUMX happens	70

The unexpected result comes from the fact, that you never do a context transition from the current context (country like Spain or in the next row Netherlands or in the next row Germany) to the row context of SUMX.

As the function is evaluated for every row, it also will show for Spain.

Does that make sense for you? I hope that was not too confusing 😅

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

Hello @selimovd ,

Thank you very much for your reply. Allow me to go a bit "deeper" into it:

1. It's still not clear to me how to "hide" Spain. This is just a dummy example (I could simply hide it using the user interface filters of PowerBI Desktop) but in my real scenario I need this FILTER-table to be computed only for the countries that have sales. I guess I need some kind of "IF" structure but I cannot figure it out.

2. What do you mean exactly by:

2.1 all the filter in the context, like the country in each row. In fact,I struggle with something: the filter context here would be the variable "_table" or what I put in the PBI user interface in the "row" section when making the matrix, in this case the field "country"? I feel there is some ambiguity in general between the "filter context" and the "query context" I read in PBI's official doc.

2.2. by the way, always write column including the table and measures without. This is the common way to write DAX. You mean to write "SalesTable[amount_euros]" instead of "[amount_euros]"? I was not even sure if it was the same... I was quite confused because the "amount_euros" I want to sum is in fact the one from the FILTER-table you mention, not the one from the "SalesTable"... I thought that was what I was doing when writing [amount_euros] without the preceding table name...

2.3 you never do a context transition from the current context (country like Spain or in the next row Netherlands or in the next row Germany) to the row context of SUMX. How should I do this transition?

Thank you very much again for you help,

Kind regards,