Return Filter Names of current Filter Context

ImkeF

Well-known Member
Joined
Feb 1, 2015
Messages
544
Is there a way to return the filter name(s) of the current filter context in a DAX-measure?

Background: Want to calculate aggregates on time series only if they are complete. I.e. if it is a monthly time series with values from Jan 2014-Aug 2015, then the aggregations for 2014 will all be shown: annual, as well as all 4 quarters. But for 2015 only the first 2 quarters aggregations shall be shown (no unfinished quarters and year).

So I need to check which filter context applies in the current cell (annual or quarter) in order to determine the necessary conditions (for an annual aggregations of monthly time series there must be 12 values reported already, whereas for a quarterly aggregation I need only 3 monthly values (..and for a quarterly time series only 4 reported values are necessary for the annual aggregation).

So the measure needs to detect the current filter context (quarter or year) in order to return the minimum-number of reported periods. This will then be compared against the actual number of reported periods and if they match, the aggregation will be shown.
So my current approach is to count the number of months in the calender and determine the selections based on that (if 12, then year, if 3, then quarter...) but as this is on very large tables, I'm looking for performance improvements.

If there is a better solution than this approach – please tell!
 
Last edited:
@ Matt,
I can very well understand if you're loosing your patience with me and step out here. Thanks again very much for your efforts.

But in case you want to know what this all was about: I've uploaded a new version of the file where I stripped out all unnecessary items and alternative measures. Also added real examples of reports. All my dropbox uploads contain the full data model, you just have to download the complete file to your computer (blue button up right: download). Anyway: If this doesn't work, please let me know or download it from OneDrive instead.
Therefore I didn't quite understand why you say that you didn't see the datamodel. Or do you mean a formal description of the datamodel?

@ anvg,
thank you very much for your answer. No need to apologize at all re language at all - but especially in this case where this might have helped you to understand my humble attempts :)
Your alternative approach calculates exactly the values that I need! But it reports them at the wrong place: All at the month-level of their date-field:

ME_ShowOnlyCompleteData4.png


If for example an unemployment rate is only calculated on a quarterly level (but reported with 30.09.2015 due to technical reasons) it means that it covers the whole range from July-September. So it would actually be wrong to show it in September, as this could lead to the false assumption that it belongs to September only. So it should only show up on the quarterly level and on the annual level, once all 4 quarters are reported.

ME_ShowOnlyCompleteData3.png


Please find an interactive file attached where you can play around with the time-slicers: They filter the "date where the figures came into the database". So actually reflecting how the reports would look like if they were made at that day.

I prepared 3 scenarios:
1) how the report would look like if it was made End of August
2) how they would look like if made End of October
3) ... End of the year.

Thanks again @all for taking the time and patience :)
 
Upvote 0

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Hi Imke

It is me that should apologise - I was reading the tread from my iPad (clearly not very well) and didn't realise you had posted a download. OK, how I have taken a look and now I understand the problem (I think).

How about you simply create 1 hard coded measure for each of these items. Then you can just use these measures in any pivot with a time based pivot table. Here are a couple I wrote - you should be able to copy the pattern.

Quarterly Total Order value - Agr - Germany:=if(COUNTROWS(VALUES(Calendar[Month]))>=3,CALCULATE(SUM(FactObservations[Amount]),DimTimeSeries2[Id]=2))

Seasonaly Adj Un Emp Rate - Germany:=if(COUNTROWS(VALUES(Calendar[Month]))>=3,CALCULATE(AVERAGE(FactObservations[Amount]),DimTimeSeries2[Id]=5))

You can see the pattern to check for the granularity of the row in the pivot in the first part of the if statement. It simply counts the rows in the pivot table. If it is a month then it will be 1 of course, 3 for a quarter, 12 for a year. With this info you can set your if statement to make the bespoke formula work for that level or return a blank. You can then hard code the second part of the formula to do the type of aggregation you need but also filter for the correct dimension.

Will this work?
 
Upvote 0
Hi Matt,
no problem, thanks for your answer - relieved to see the reasons.

Challenge with my datamodel are around half a million different time series (rows in the dimension table). So even if Scott would finally publish his trick to inject measures back into the model (which we would have automatically created using Power Query) - this would probably blow up the model :)

But I think I got at least one answer here: That it is not possible to directly return the filter names in a way that would cost less performance than the COUNTROWS-operation.
 
Upvote 0
Hi, ImkeF
Maybe I think it will be faster if we will use pre-aggregation by Power Query? I haven't enough data for a test this assumption ;) . Could you check a such solution Zippyshare.com - Demo2.xlsx
Regards,
 
Upvote 0
Hi anvg,
this would definitely be a good alternative if these were the only reports to be made based on those data. But as I have so many other measures as well, in my scenario I'd have to add these tables to the existing fact tables. As far as my DAX-knowledge goes, this is not the best way to build a model - but who knows - needs to be tried out.
Thanks a lot and BR,
Imke

BTW: second file not working - producing error-message like "0-values cannot be added to the pivot-table" .
 
Upvote 0
Hi, ImkeF!
I tried to test my Power Query solution on random data. I am sorry, but it works too slowly.Probably I have a problem with Excel 2016 64-bit on my computer (I cannot deinstall or repair its installation at all). Maybe a version with a sql database table for FactObservations will be executed fast by using pre-aggregation queries.
Your measure is calculated as for me quick, but with that random data it has a wrong output. I change your measure to
Code:
:=Var monthCount = DISTINCTCOUNT('Calendar'[YearMonth])
Var minMonth = MAX('DimTimeSeries2'[MinCountMonth])
Var minQuarter = MAX('DimTimeSeries2'[MinCountQtr])
Var minYear = MAX('DimTimeSeries2'[MinCountYear])
Var result = IF(monthCount = 1,
    IF(monthCount>=minMonth && NOT(ISBLANK(minMonth)),[AVG],BLANK()),
    IF(monthCount=3,
        IF(monthCount>=minQuarter && NOT(ISBLANK(minQuarter)),[AVG],BLANK()),
        IF(monthCount>=minYear && NOT(ISBLANK(minYear)),[AVG],BLANK())
    )
)

RETURN result
Do you known why a blank value is smaller than number? I thought that it is like null value in SQL. Comparing number with null is always false in SQL because null is undefined value. One may be greater or smaller than number.
I don't understand why second version runs with a mistake. I uploaded a new one with a full calendar. Zippyshare.com - Demo2.xlsx
Regards,
 
Upvote 0
Thanks a lot - file now working.
Very good idea to use variables - will give it a try as well!
Strange about the wrong output on random data. Could it be that the calendar doesn't cover all data in the fact/obervations table then? The checks on my data were correct.

No expert on SQL-server, so the wrong person to ask :)
For this project data will be cleaned before entered into the DB.
Thanks a lot again!
 
Upvote 0
Hi, ImkeF
Strange about the wrong output on random data. Could it be that the calendar doesn't cover all data in the fact/obervations table then? The checks on my data were correct.
It was not your calendar table. It happened because
1. The random data contains 3 months per quarter and 12 months per year for each RAId
2. You measure contains restrictions for same TSId by count of months for quarter and year which is not equal 3 and 12. You use = comparing but >= or <=.

I uploaded my file with random data for your testing to Zippyshare.com - Demo3.zip, but it is big - 26Mb.

May be I found an partial answer for your direct topic question "Return Filter Names of current Filter Context"
I used a such solution for selecting sum or average measure is calculated for requered TSId item.
Code:
measure mode:=Var calcType = DISTINCT('DimTimeSeries2'[type])
//if it is a single value then one is a row filter and then we can use it for selecting a returning measure
Var doCalc = IF(COUNTROWS(calcType) = 1,
    SWITCH(calcType,
    "sum",[Amount sum],
    "average", [Amount average],
    BLANK()
    ),
BLANK()
)
RETURN doCalc
Regards,
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,240
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top