Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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.
@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.
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?
In between, you can refer this doc: https://www.sqlbi.com/articles/introducing-summarizecolumns/
@Anonymous ,
You can simply take sum of Revenue in visual. Not able to get advantage of summarizing column?
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.
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |