Help with FILTER: "multiple columns" error

[PaulDBrown]

Hello everyone. This is my very first request, and I confess I am very new to Power BI so please bear with me...

Ok, SO I'm trying to do apply  basic filter to a table, but I'm failing dismally.

Basically I would like the measure to filter the table to display only the columns which meet the expression, but I get an erro message.

here is the screenshot:

Wht exactly is wrong with the measure??

Thank you for your help!

Paul.

[igor]

You can use this formula.

YEAR 2014 = IF('table'[year]=2014,2014)

[PaulDBrown]

Thank you, that of course makes sense...

The reason I'm trying to apply this measure is actually to simplify other measures by using this new measure (the filter on year...) as a nested measure to other measure.

This it how I'm trying to make things work. I have a model where a period table (seen in the screenshot) acts as the lookup table to be able to filter data tables with differing granularity (sales table - high ganurality with transcations on a single date level- and a budget and monthly estimates - with a lower granularity ie month level).

Using this period table (YearMonth), I have succeded in writing mesures to filter results based on current month, YTD, same month last year, or YTD previous year. One example of these period measures is:

Previous YTD Sales  = CALCULATE([Sales];
 filter(YearMonth;
  YearMonth[Year]=YEAR(TODAY())-1
  &&
  YearMonth[month]<=MONTH(TODAY())))

This apears to be working - please bear with me because this is my first attempt at Power BI hving read two books and done the "preiliminary" Youtube video watching...

In order to make things simpler, what I'm now trying to do is to fold the Year and month references into simpler measure to nest them in all further measure involving other calculations In other words to avoid having to type in the whole filter expression used in the "Previous YTD Sales" example every time I need to write a new measure... So Ideally I am trying to write a measure which equates to:

Current Year defined as YearMonth[Year] = YEAR(TODAY())

and

Current Month defined as YearMonth[month]=MONTH(TODAY())

plus the different options for previous year etc...

So the first thing I tried was to create a measure by filtering the YearMonth table. This satrted off as

Current Month = FILTER(YearMonth; YearMonth[month] = MONTH(TODAY()))....

But was getting the error mentioned in my original post...

I guess I can always just past the expression into a notepad and copy and paste when needed, but if there is a simpler way....

Many thanks again for your help!

Paul.

[KHorseman]

Ah, I see what you're going for. Since you can make base measures that can be referenced and filtered in other measures, you want to be able to have a premade filter declaration that you could plug in the same way. Unfortunately that's not possible. Measures aggregate to a single scalar value. What you're proposing would have to return a table, so measure-like filter declarations would have to be a completely different type of data from measures, and that currently doesn't exist.

The closest we have is the New Table button, which allows you to define naked tables in DAX. The formulas look like what you've written. You can't really use them in the plug-and-play method you're imagining though, and the tables exist in memory even if there's no other measure referencing them at the time, so it's not really a substitute for what you want.

I actually proposed the same idea to some developers at the Data Insights Summit last year. I might submit it to the idea forum to see what the community thinks.

[PaulDBrown]

@KHorseman

Thank you! I'll explore the new table option.

[LaurentCouartou]

Note, that for the example you provided, you could just add a column called PreviousYTD defined as:

PreviousYTD= YearMonth[Year]=YEAR(TODAY())-1
  && YearMonth[month]<=MONTH(TODAY())

This is just a regular boolean expression, you could use as:

Previous YTD Sales  = CALCULATE([Sales];
   YearMonth[PreviousYTD]
  )

 This would work, since in your case the definition of the previous year is not dynamic (at least not depending on the user's selection).

(Usually, when people talk about about Previous year calculations, it means "one year before the currently selected date").

[PaulDBrown]

Thank you again, KHorseman.

I think it would be useful if we could hace a way of storing expressions for future use which don't actually compute until used in a proper context. Even a notepad type of functionality might do...

As it is¡ I have started a document with "most frequently used" expressions and just copy and paste...

Something else I wish I could do is classify measures into categories regardless of tables; It would really make life much simpler...

Thanks again!

Paul

[KHorseman]

Well there is good news on the last bit. You can use Enter Data to create a dummy table with nothing in it, then attach measures to that table. Measures can go anywhere. So just make tables for each category you want. The measure tables will all be stuck with one empty column and I don't think there's anything you can do about that, but it's only mildly ugly.

I like your expression scratchpad idea. You might want to submit that.

[PaulDBrown]

@KHorseman

I think the scratchpad idea is a bit clumsy for what I'm trying to do, but might add more value for other things....

What I think 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 uptdating day by day, month by month etc...Added to all this, you have a high granulartiy 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 workk 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 kinfd 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 expressiones 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?

[KHorseman]

@PaulDBrownnot preposterous at all. That's exactly the sort of thing I was envisioning: reusable measure-ready table/filter definitions. ReMReTFiDs.

...

That is not a better name than FUSEX. 😛

Write up a clear description and post it to the Ideas forum. Maybe we can drum up enough votes to get it noticed.

[LaurentCouartou]

I am all for this feature. I think this is would be a big step Power BI / SSAS Tabular.

Other people have recently submitted this idea:

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/14950908-add-global-variables-to-...

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/11550576-allow-measure-like-table...

[KHorseman]

Voted.

[PaulDBrown]

@KHorseman

@LaurentCouartou

@igor

Ok, so I've submitted FUSEX as an idea...

I confess to being lazy when it came to the submission content...

[KHorseman]

A measure has to return a single value. FILTER returns a table, not a single value. What are you expecting this measure to do?

[PaulDBrown]

Thank you, that of course makes sense...

The reason I'm trying to apply this measure is actually to simplify other measures by using this new measure (the filter on year...) as a nested measure to other measure.

This it how I'm trying to make things work. I have a model where a period table (seen in the screenshot) acts as the lookup table to be able to filter data tables with differing granularity (sales table - high ganurality with transcations on a single date level- and a budget and monthly estimates - with a lower granularity ie month level).

Using this period table (YearMonth), I have succeded in writing mesures to filter results based on current month, YTD, same month last year, or YTD previous year. One example of these period measures is:

Previous YTD Sales  = CALCULATE([Sales];
 filter(YearMonth;
  YearMonth[Year]=YEAR(TODAY())-1
  &&
  YearMonth[month]<=MONTH(TODAY())))

This apears to be working - please bear with me because this is my first attempt at Power BI hving read two books and done the "preiliminary" Youtube video watching...

In order to make things simpler, what I'm now trying to do is to fold the Year and month references into simpler measure to nest them in all further measure involving other calculations In other words to avoid having to type in the whole filter expression used in the "Previous YTD Sales" example every time I need to write a new measure... So Ideally I am trying to write a measure which equates to:

Current Year defined as YearMonth[Year] = YEAR(TODAY())

and

Current Month defined as YearMonth[month]=MONTH(TODAY())

plus the different options for previous year etc...

So the first thing I tried was to create a measure by filtering the YearMonth table. This satrted off as

Current Month = FILTER(YearMonth; YearMonth[month] = MONTH(TODAY()))....

But was getting the error mentioned in my original post...

I guess I can always just past the expression into a notepad and copy and paste when needed, but if there is a simpler way....

Many thanks again for your help!

Paul.