Skip to main content

Power BI

Needs Votes

Include a functionality to store and select Frecuenlty Used Expressions (FUSEX)

Vote (2) Share
PDB's profile image

PDB on 30 Mar 2017 22:20:37

I think it is worth exploring is the idea of being able to include expressions as part of the selectable objects. IE. we have Tables, Columns, Measures...why not include Frequently used expressions (FUSEX so to speak...)?
 
Imagine you had an expression you had to use frequently. In my case, I'm building a model where the most relevant information is Current month and Current Year results compared to the same periods in the previous years, aswell as including flexibility to be able to select time periods even within the same page in a report (which means you cannot use a slicer and filtering by visual is impossible (?) if the report will be updating day by day, month by month etc...Added to all this, you have a high granularity sales data table, but your Budget and estimates tables are on a monthly basis, which involves setting up a period table and renders Time Intelligence functions meaningless when comparing values from high and low granularity...
 
So it comes down to how do you establish the current month and year (+ period comparisons)? As far as I have been able to work out, you have to filter tables manually in CALCULATE functions, or add calculated columns (which as far as I have read in a couple of books you should avoid if no other alternative is possible: ie a measure cannot be computed.
 
So what I have been doing in this model is computing a filter within a CALCULATE  function to define the time period I need to establish, such as:
FILTER(YearMonth;
YearMonth[year]=YEAR(TODAY () )
&&
YearMonth[Month]=MONTH)TODAY () )
 
Where YearMonth is the period table which has a relationship with the 'sale's, 'budget' and 'estimates' tables.
 
Now since i use these kind of expressions constantly, I have resorted to typing them in a word document and copy/pasting them. Which is fine, it saves time, But in a team work environment it is not efficient.
 
So my suggestion is to enable Power BI to "store" expressions as another selectable item, just as we have Tables, Columns, Measures...
 
The idea being that we could write expressions (FUSEX) such as:
Current Month Expression=
FILTER(YearMonth;
YearMonth[year]=YEAR(TODAY () )
&&
YearMonth[Month]=MONTH)TODAY () )
 
and use a suitable wrapper such as {.....}
 
These expressions would then be stored and available in the field menu (or a new expressions menu) and could be computed in the form of:
 
[Total Sales Current Month] = CALCULATE(SUM('Sales'[Sales]);
                                                 {Current Month Expression}
 
Et voilà...
 
Does that sound preposterous?