Using Measure as a Visual level filter

[ECE]

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?

[v-juanli-msft]

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]

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 · Accepted solution]

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

[Anonymous]

 I have very simlar problem: I cannot remove the visual level filters. - now i have set to the one value. 

Can you advise how to remove it?

thank you

[ECE]

Hi again,

I am pretty sure you solution actually solved my problem for a while.

However, now the problem is back - data is not being filtered.. That means, at least not in Power BI Desktop, but it still works in Power BI online, as long as I am not in edit mode... I'am confused..

Is there supposed to be a difference between desktop and online version ?

Is there any other way, I can use a meassure to filter data in a visual, in the same way a slicer filters data?

Hope you can help,

Best regards,

Espen

[ECE]

Hi Maggie,

Thank you very much for your help - that did the trick 🙂