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

Basic understanding of context, simple example

Hello,

 

Below is a simple exercise I made in order to try to understand/learn the concept of evaluation context.

 

The table shows projects, department, month and budgetrevision and budget. BudgetMax is a measure I've created in order to get the latest budgetrevision.
In the clustered column chart I've highlighted the colum for 201807, which then filters the table. However, what I really want BudgetMax to do is show the latest Budget amount (based on revision number), for each project. As you can see, the table shows there are 2 unique project numbers in 201807, and so I want the BudgetMax measure to show 20 000 + 30 000 = 50 000 (both in the table and in the chart).

 

As you can see from the BudgetMax measure, I'm trying to combine sumx & summarize to solve the problem, without really understanding how they work.

 

Any help would be greatly appreciated.

 

TL-DR: Want measure to show latest budget per project, per month

Capture.PNG

7 REPLIES 7
Anonymous
Not applicable

can you screeshot your model?

Anonymous
Not applicable

there is no model currently, only this table. This is just to get an understanding of the basics.

Capture1.PNG

Anonymous
Not applicable

so, when you choose your 201807 in the graph, you're filtering out the three rows 201807. Your filter context (albeit simple as you have only one table) is that, those three rows.

Now if you want to show only a single budgetrevision( as an example) you'll have to put another slicer with the budgetrevision column and when you select "4" your filter context will be the union of 201807 and 4.

 

If you put a table with MonthKey in columns and Project in rows, each cell of your table will have a specific filter context, made by the union of those values of that column.

So you then create a measure 
Budget=SUMX(table;table[budget])


When you put that measure in the VALUES field of your table, it will calculate the values for each cell, considering the filter context of that cell, so in fact showing the data as you want.

 

Anonymous
Not applicable

thanks for the reply, but I obviously can't use budgetrevision in a slicer, since I don't know the number of the last revision for each and every project (in a more realistic example). That's why I'm using Max, in order to return the highest value of revision.

 

I realize that my measures the evaluation context changes based on the visuals I use, but I need to manipulate my measures to show the context I want, even if the visual context might be different.
Example; If I just wrote a measure that returned [budget] based on max[budgetrevision] and showed this in a month by month chart, it would only show 1 budget for 1 project - I need it to show the "latest" budget for all the projects in this particular month - and this is where I need some help

Anonymous
Not applicable

Create a measure

 

MaxBdgRev =
VAR maxrev=MAXX('table';'table'[Revision])
RETURN
sumx(FILTER('table';'table'[Revision]=maxrev);'table'[Budget])
 
How it works. The VAR will find the MAX value (iterating the current filter context) of revision (in that context).
Then the SUMX will work on a new table, filtered where revision is the max previously calculated, and then "sums" the budget. I know you don't need to SUM as it's one value, but you always need to iterate.

I changed a bit your dataset to show that it works. In the filter [201803 , 1234 ] the max budget is 20.000 but the revision 4 has a lowest value which is 12.000. So if you look at the graph:
2019-01-18_1405.png
MaxBudget calculates the max, while maxbdgrev is the value where rev is max.
Download the pbix at the previous link
 
Anonymous
Not applicable

Thank you for the reply. This helped me along the right path I feel, even though this doens't look entirely correct either. In retrospect, I think my description of the problem is somewhat lacking, so I've decided to make a new thread with more detail and expand the problem abit.

 

Thanks again.

Anonymous
Not applicable

Actually, you don't really need to create the measure. You set up the graph with "SUM" as aggregation . see here:

https://drive.google.com/file/d/1vjstOO638dLNFusq5HvGM2y7JuFppxdB/view?usp=sharing

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.