Combined slicer

[AndrejZitnay]

Hello Guys,

It is possible to combine two slicer into one?

Data are in two different data sheets and there isn't relationship between data. I am visualizing data from two data sheet in one Page.

Currently I have to set up slicer for both data sources.

I need to combine :

1st slicer – there are only two words Apple & Microsoft

2nd slicer – dates in exactly same format

I need to have 2 slicers instead of 4.

Is this possible?

Many thanks.

Andrej

[v-jiascu-msft · Accepted solution]

@AndrejZitnay,

Hi Andrej,

A slicer works on its parent table or works through the relationship. So we need to find out a way to establish relationships. Here we go:

1. Create two new tables. 

DateTable =
DISTINCT ( UNION ( VALUES ( Table1[Date] ), VALUES ( 'Table2'[Date] ) ) )

NameTable =
DISTINCT ( UNION ( VALUES ( Table1[Name] ), VALUES ( Table2[Name] ) ) )

2. Establish relationships.

3. Create slicers with the column from new tables.

Best Regards!

Dale

[v-jiascu-msft · Accepted solution]

@AndrejZitnay,

Hi Andrej,

A slicer works on its parent table or works through the relationship. So we need to find out a way to establish relationships. Here we go:

1. Create two new tables. 

DateTable =
DISTINCT ( UNION ( VALUES ( Table1[Date] ), VALUES ( 'Table2'[Date] ) ) )

NameTable =
DISTINCT ( UNION ( VALUES ( Table1[Name] ), VALUES ( Table2[Name] ) ) )

2. Establish relationships.

3. Create slicers with the column from new tables.

Best Regards!

Dale

[AndrejZitnay]

Hello Dale,

You are star.

Many thanks you for your help.

I managed to sort out name table.

I have small issue with date table as I have there some blanks but I should be able to sort this out on my own.

BTW this will help me as well in other reports where I can build now this relationship.

Many thanks again.

Kind regards.

Andrej

[v-jiascu-msft]

Hi Andrej,

It's my pleasure. I am so glad it helps.

Best Regards!

Dale

[AndrejZitnay]

Hello Dale,

Hope you are well.

I have one additonal question.

This is my Date formula expresion :

Date Table = DISTINCT(UNION(VALUES(Terminations[Date of Renewals]),VALUES(Renewals[Date])))

Everything is good but I have null values in my Date Table and I can't create relationship.

Is there any way how to avoid that?

Sort of -  if null than exclude?

I am still learning to work with DAX formulas.

Can you help me out?

Many thanks.

Kind regards.

Andrej

[v-jiascu-msft · Accepted solution]

@AndrejZitnay,

Hi Andrej,

I am so glad to help. You can try to add a filter function like this. Add a "[" after "isblank(", then there will be a hint of column that can be selected.

Date Table =
FILTER (
    DISTINCT (
        UNION ( VALUES ( Terminations[Date of Renewals] ), VALUES ( Renewals[Date] ) )
    ),
    ISBLANK ( [Date of Renewals] ) = FALSE ()
)

Best Regards!

Dale

[AndrejZitnay]

Hello Dale,

Thank you very much.

I had to change cross filter directions in  name table as it was fighting somehow with date table but I have now what I needed.

Have a great day.

Kind reagards.

Andrej