Forum Discussion

MarkSL's avatar
MarkSL
Helper V
8 years ago
Solved

Filter using custom date options

Hi,

 

I want to create simple slicer which includes custom date options, such as:

 

 

 

I know I can use a Relative Date Slicer, but I would like to keep my filter really simple if possible.  I am sure this has been done before but I seem to be going around in circles looking for a solution.

 

The approach I have started is to create my own DateControl table, with one row per date and columns for each custom date option:

 

 

And I have created a relationship from this to my main table, joining on date.

 

I have then created a third standalone table, simply listing the different categories, which my slicer points to:

 

 

I was trying to following this guide, which create a cusom measure, but I can't get it to work.  From what I can tell, it will select the records with the maximum value from the column appropiate to what has been selected in the filter.  So in my case, if I select 'This Month', it will select rows from column DateControl[This Week] where the value is 'Y.  

 

I have also tried updating my measure to use the following code, but still the report does not change when I select from the filter:

 

 

IF (ISFILTERED ( DateOptions[Category]) && HASONEVALUE (DateOptions[Category] ),
SWITCH(LASTNONBLANK (DateOptions[Category], 0),
"This Year", MAX(DateControl[This Year]),
"This Month", MAX(DateControl[This Month]),
"This Week", MAX(DateControl[This Week]),
"Last Week", MAX(DateControl[Last Week]),
"Yesterday", MAX(DateControl[Yesterday]),
"Today", MAX(DateControl[Today]))
BLANK()
)

 

Any help or suggestions greatly appreciated!

 

Mark

 

  • If anyone is interested, I was referred to the following article on Chris Webb's BI Blog which provided me with the solution I was looking for.

9 Replies

  • Hi,

     

    Apologies, please bare with me, quite new to all of this!

     

    Ok, very usefully, I've now been able to see my measure, DateCategory, in action by adding it to a simple report.  When I change my Date Category slicer, I can see that the measure dynamically updates, putting a 'Y' in all cells that match the selection.  So if I select "This Week", I get two Y's, mimicking the two Y's in my This Week column:

     

     

    All I need to do now is to be able to filter on this measure, so only those two records are used - but it seems you can't.  Am I close or going about this all wrong?!

     

    Thanks

     

    Mark

    • v-jiascu-msft's avatar
      v-jiascu-msft
      Microsoft Employee

      Hi Mark,

       

      It seems the measure worked. What's the issue? Do you mean the table visual should only show two rows and hide the other rows?

       

      Best Regards,

      Dale

      • MarkSL's avatar
        MarkSL
        Helper V

        Hi Dale,

         

        Thanks for taking a look and yes that is correct, I want the table to be filted to those two rows, thereby also filtering my main data table which is joined to the date table, to only return data for those two dates.

         

        Perhaps using this method is not viable?

         

        Thanks


        Mark