Forum Discussion
Help with FILTER: "multiple columns" error
A measure has to return a single value. FILTER returns a table, not a single value. What are you expecting this measure to do?
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.