Using a Filter with SummarizeColumns

[Anonymous]

New day, new question.

I have an issue with a project I am working on.

In this simplified example, I have a table, t_Revenue with fields Project, Year and Revenue.

Project	Year	Revenue
A	1	10
A	2	20
A	3	15
B	1	15
B	2	20
B	3	25

I also have a table t_Years with a field Year which is a simple list 1,2,3.  There is a slicer attached to the Year field and a relationship between t_Revenue[Year] and t_Years[Year].  If I use the slicer then the table t_Revenue is suitably filtered.

I have written some DAX SUMMARIZECOLUMNS code to create a dyamic table.  The purpose of this table, t_Summary is to show the total revenue for each project, filtered by the year.

The code for this is 

t_Summary = SUMMARIZECOLUMNS(t_Revenue[Project],t_Revenue[Revenue])

Showing t_Summary in a table visual shows that the creation of t_Summary is ignoring the filter placed on the Year field in the t_Revenue table.  Is this to be expected, am I doing something wrong, how can I achieve this?

Thanks.

[Greg_Deckler]

There is an optional filter table argument for SUMMARIZECOLUMNS:

t_Summary = SUMMARIZECOLUMNS(t_Revenue[Project],t_Revenue[Revenue],FILTER(...))

https://docs.microsoft.com/en-us/dax/summarizecolumns-function-dax

[Anonymous]

@Greg_Deckler 

Just asking out of curiosity, the table t_Summary will be created and stored persistently in the data model before the user selects anything in the UI/Slicer. After the creation of the table, it does not matter what the user selects in UI, the contents of the table are not gonna change. The FILTER() argument on SUMMARIZECOLUMNS is useful only if we know the filter condition or values at the report design phase. That is my understanding.

Are you saying that it is possible to capture the user's filters applied to the UI can be captured and passed on to the t_Summary table in a scenario where the t_Summary is not calculated in a measure and it is a table that is stored persistently in the data model?  If that is possible, it will open up a lot of possibilities.  My questions are in a generic sense. But in this case, I think the t_Summary table itself is not even necessary for the purpose stated here as the project-based figures can be calculated based on the year/date filter even without the t_Summary table. 

[Greg_Deckler]

@Anonymous it depends. If that formula is used to create a table, it is persistent. If that formula is used within a measure, it is dynamic.

[Anonymous]

@Greg_Deckler 

True. 

In @Anonymous 's case, it is used as a persistent table. Therefore the filter is not gonna work. It would not be possible to filter the t_Summary table dynamically based on the  Year slicer selection. That is the conclusion. Correct?

[Greg_Deckler]

No, I would create a measure structured like this:

VAR __Table = SUMMARIZECOLUMNS(...)
RETURN
<some calculation over __Table>

Then it is dynamic and can take the year filter into account.

[amitchandak]

In between, you can refer this doc: https://www.sqlbi.com/articles/introducing-summarizecolumns/

[amitchandak]

@Anonymous ,

You can simply take sum of Revenue in visual. Not able to get advantage of summarizing column?

[Anonymous]

OK, but the example I mentioned illustrates a specific issue I have with a much larger piece of work.  It isn't possible to share the project and so I have condensed the issue I am having in to the specific question asked.  Because of it's simplicity I am sure that it's a non-issue but sadly not in the work I am doing.

[amitchandak · Accepted solution]

@Anonymous , Let us take it like this. If you take the filter and create a table, a new table it will not be dynamic. But it is created part of measure it will be dynamic.

We use summarize columns, summarize to have sql subqueries. sometimes we force a row context

example

measure
=sumx(summarize(sales,sales[customer_id], "_Cnt",count(sales[customer_id])),if([_cnt]>1,1,0))

Or take max till project level and then sum

Or sum project having revenue >10000