Forum Discussion

ECE's avatar
ECE
Advocate II
8 years ago
Solved

Using Measure as a Visual level filter

Hi,

 

I have a table where I would like to apply a dynamic date filter, in the form of a measure, as a Visual level filter.

The table contains data from two different sources, both containing a "Calendar Day" field. These Calendar Day fields are linked to a third DateTable where I have a distinct list of dates. The table should not show the Date field, only filter upon it.

 

When I insert a normal slicer in the report with the DateTable[Date] field, the data in the table can be filtered as expected. However, when I try to apply my measure to the visual level filter, the filter only works as long as the Date filed is included as column to be showed in the table. Othervise the table shows no content.

 

The formular for the measure I use is:

TestFilter1 = if(MIN(DateTable[Date])>NOW()-28;"Show")

 

I apply the above measure to the table visual level filter and set it to "show".

 

Since the table is in fact filtered when the Date field is included as a column, I think the measure is OK, but why is it not working withouth the Date field showing?

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    8 years ago

    Hi ECE

    As tested, when the values to be show are the same, the filter using measure doesn’t work.

    But, when the values are different, it works.

    A workaround is to modify the previous formula to the following one

    TestFilter1 = if(MAX([Calendar Day])>NOW()-28,"Show")

     

     

    Best Regards

    Maggie

6 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    8 years ago

    Hi ECE

    I make a test as you discrebed.

    As far as I known,Measure "TestFilter" uses MAX([Calendar Day]) to define a row content, so if there is no "Calendar Day" column in the table, it can't work as before.

     

    Best Regards

    Maggie

    • ECE's avatar
      ECE
      Advocate II
      8 years ago

      Thanks for your reply.

      That was also what I got to.

       

      However, I was looking for a way to use a measure to filter data in the same way as a standard slicer can filter data. A slicer can filter on fx Calendar day, even if Calendar day is not present in the table.

      • v-juanli-msft's avatar
        v-juanli-msft
        Community Support
        8 years ago

        Hi ECE

        As tested, when the values to be show are the same, the filter using measure doesn’t work.

        But, when the values are different, it works.

        A workaround is to modify the previous formula to the following one

        TestFilter1 = if(MAX([Calendar Day])>NOW()-28,"Show")

         

         

        Best Regards

        Maggie