"Measure-Like Thingies" that return tables/filters

[Anonymous]

I kinda want to say standard analysis server cubes have this concept, but I dunno what to call it.  I was born when DAX was born, not MDX 🙂

There are times when I would love to pre-define a filter... basically like adding my own dax function.  Say, I have a custom calendar, and I define my YTD as:

... VALUES(Calendar[Year]),FILTER(ALL(Calendar), Calendar[WeekId] <= MAX(Calendar[WeekId]) && Calendar[WeekId] >= [First Valid WeekId]))

Copy and pasting that onto Revenue_YTD, Margin_YTD, Cost_YTD, etc... it feels icky.   I would love to define it once.

Any plans for that hotness?

[ALeef]

I would use Calculated Tables, like @WillT mentioned.  I have used them extensively in my projects.  Take your main key (whatever you are filtering on generally) to get your first column.  Then you can add additional columns with = RELATED(yourfirsttable[yourfirstcolumn]).  Then if your model ever changes, or you need to update what is getting filtered (1 wk vs 4 wks etc..), you just have to change the table definition, not every column.

This is also really helpful for combining queries when you only need a few of the columns from another table, and not the whole query - assuming you can have a common key to make a relationship with.

[leonardmurphy]

@ALeef do you have an example that you could share?

I understand the concept of calculated tables, but I'm not following how to use them with measures.

I have, say, 25 measures already defined (let's say they all just sum various things). I now want a YTD version of each measure, without having to create 25 additional measures. How do I define my calculated table?

[greggyb]

A calculated table is not dynamic at report evaluation time. It does not fill the role that @Anonymous is describing. Calculated tables are only evaluated at dataset refresh time, and thus can't be used to define a [YTDFilter] (for example) filter that is reusable across other measures.

This is something I've yearned for since I started using DAX. So far, the only thing that can be dynamically returned at runtime is a scalar. There can be tables in the process of getting to the scalar, but no table value can be returned dynamically.

[ALeef]

@greggyb : @leonardmurphy

Greggyb is correct in that the calculated tables are only refreshed with dataset refresh, but I don't know if that is what you need.

Measures respect applied filters.  So, you *should* be able to create all your measures in the "everything" table, and then create a second visual with a filter you set (right click on the date column, add filter), and set the filter to be only this year.

I use calculated tables to sort tickets in the IT department by completion status.  So i have a CALCULATETABLE (FILTER ...)that only pulls in a list of ticketids where status = completed, or status = new.  Then I create measures off of that, and apply filters for things like YTD.

[leonardmurphy]

To see if I'm following...

It sounds like you have all your base measures in a calculated table, that may or may not be filtered.

Then on the visualization side, you add a filter for YTD or MTD and those visuals are then filtered to YTD or MTD as you need?

---

I see that being working for many, if not most scenarios. I might be thinking too much in terms of pivot tables. In a pivot table, I want to put yesterday's sales, MTD sales and YTD sales next to each other. In which case I need a separate measure for each. But, in a typical Power BI report, I'm not sure I would mix different time frames in the same visual in the same way. 

The other scenario where I see the need for separate measures is if I want to plot cumulatively by day. E.g. I can show cases opened each day by filtering the visual to YTD. But if I want to show cases opened YTD cumulatively each day (so that each days number includes the prior days number), I can't do that with a YTD filter on the visual - I need a YTD measure. 

[KHorseman]

I think I get what's being asked for here, and if so it's something I've been thinking about recently too. I don't think calculated tables are the answer; I think this really is just a neat idea that currently has no matching feature.

Dax formulas already have reusable expressions. Say for instance you define a measure called ItemCount, which is :=DISTINCTCOUNT(MyTable[ItemID]). Later on you can do things like SpecialCount :=CALCULATE([ItemCount], FILTER(MyTable, MyTable[ItemProperty] = "Special")), right? Or maybe you have an even more complex filter that combines several different columns in an AND statement, or maybe you have some time intelligence going on. Now, have you ever had to rewrite the exact same FILTER(blahblahblah...) in five different measures? Say for instance you want SpecialCost, SpecialSalesPerMonth, SpecialAverageProfit, etc.

So you already have some reusable base measures like ItemCount, ItemCost, ItemSalesPerMonth, etc. It'd be nice to have some sort of similarly reusable filter-measure thing where you just define something that returns a table or filter context. That way you could just write something one time and call it SpecialFilter, so later you could just reference it like :=CALCULATE([ItemCount], [SpecialFilter]).

[leonardmurphy]

@KHorseman that's really well worded and a well-thought through idea for how to implement too. 

EDIT: Was thinking that it'd be nice to be able to pick the special filter (such as YTD) dynamically on the visualization side, so you don't have to create & maintain dozens of extra measures...but realized that doesn't work in many scenarios (e.g. if you want to plot Sales against SpecialSales). Ignore me.

[leonardmurphy]

Thanks for the confirmation @greggyb

Any recommendations for ideas to support that would address this problem?

It looks like this one would eliminate the need to create dozens of measures for each time period, at least for a regular calendar: https://ideas.powerbi.com/forums/265200-power-bi/suggestions/6709505-analysis-by-date-time-view-data...

[WillT]

Hi Scott - take a look at Calculated Tables, they might be just what you need here... https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-calculated-tables/ HTH!

[WillT]

Hi Scott. No plans for that as yet, but it sounds like an interesting idea! Please submit it on our Suggestions forum: https://support.powerbi.com/forums/265200-power-bi Thanks!