Forum Discussion

AndrejZitnay's avatar
AndrejZitnay
Icon for Post Patron rankPost Patron
9 years ago
Solved

Combined slicer

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. C...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    9 years ago

    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's avatar
    v-jiascu-msft
    9 years ago

    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

v-jiascu-msft's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

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's avatar
    AndrejZitnay
    Icon for Post Patron rankPost Patron
    9 years ago

    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's avatar
      v-jiascu-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      9 years ago

      Hi Andrej,

       

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

       

      Best Regards!

      Dale

      • AndrejZitnay's avatar
        AndrejZitnay
        Icon for Post Patron rankPost Patron
        9 years ago

        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