How to ignore the slicer to show all values?

[AiolosZhao]

Hi all,

If I have data:

D1	V1
2017	1
2018	2
2019	3

And I have a table to show that, a slicer for D1.

What I want is, when I choose 2019 in D1, I want to show:

D1	V1
2017	1
2018	2
2019	3

when I choose 2018 in D1, I want to show:

D1	V1
2017	1
2018	2

is that possible to do that?

Thanks.

Aiolos Zhao

[rajulshah]

Hello @AiolosZhao ,

I guess you need to take care of the functionality in the measure itself.

In slicer, I think it is not possible to do this, because it selects a value and has effect on the charts. Maybe this is possible using two slicers.

[AiolosZhao]

Hi @rajulshah ,

Thanks for reply, I just want to ignore the current selections like I said,

and show dimensions which I want in my table or chart.

for example, I have a year selection, and I have a measure to calculate the count id.

So user can choose every year to see the measure(I use card chart to show it).

and at the same time I also want to show the trend to users, so I need to ignore the user selections

but I don't know how to do that.

Thanks

Aiolos Zhao

[TomMartens]

Hey @rajulshah ,

you have to be aware that no value is selected, the visual level filter just hides the values from the slicer, I guess you have seen the video https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-june-2019-feature-summary/

and also read the article from the sqlbi guys: https://www.sqlbi.com/articles/syncing-slicers-in-power-bi/ 

Basically the requirement of @AiolosZhao is the reason, why this feature has been finally implemented.

Regards,

Tom

[AiolosZhao]

Hi @TomMartens ,

thanks for reply, I'm using the latest Power BI Desktop, and just now I saw the new feature.

To be honest, I have simplified the data model when I create the topic, in my read data,

my V1 is a measure, not a column.

I'm not sure whether it's also possible for a measure?

Thanks.

Aiolos Zhao

[TomMartens]

Hey, 

yes it's possible, but you have to use the latest Power BI Desktop release (June 2019) and you have to create a star schema, meaning create dimension tables for V1 and D1 like so:

I created both tables by using this simple DAX statements:

D1 = DISTINCT('Table2'[D1])

and

V1 = DISTINCT('Table2'[V1])

After I created both tables I created the relationships (the beginning of a start schema 🙂 )

I created this measure and assigned it to the table V1:

vizAid slicer show V1 = 
var thisD1 = MAXX(VALUES(D1[D1]) , 'D1'[D1])
var thatV1 = MAXX('V1' , 'V1'[V1])
var theVs = 
        FILTER(
            ALL(Table2)
            , 'Table2'[D1] <= thisD1 && 'Table2'[V1] = thatV1
        )
return

INT(NOT(ISEMPTY(theVs)))

I use this measure as visual level filter on the slicer of V1, this a new feature that has been introduced with the June 2019 release of Power BI Desktop.

And the result, please be aware the content of the slicers is coming from the new dimension tables.

Hopefully this is what you are looking for.

Regards,

Tom