Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Relationships between two columns

Hi there,

 

I have 3 tabs in my data file, each with a 'time period' and 'visitor type' column, which I wanted to join the files by, and also use as slicers. Unfortunately, I can only create a relationship between the 3 tables using the time period column. 

 

Is there any way I can also link by 'visitor type'? So then I can have 'time period' and 'visitor type' as slicers across all data?

 

 

 

I hope this makes sense! 

 

Also worth noting, my time period column is exported as aggregated periods of time 'last 7 days, last 30 days' etc., not as an actual date group. 

 

Thanks, 

Sonia

Need Help
Tips and Tricks
  • jdbuchanan71's avatar
    jdbuchanan71
    6 years ago

    Hello Anonymous 

    You can create two lookup tables that contain the Visitor Types and Time Periods from all 3 tables.  Then you hook all 3 tables into the look tables and pull your slicers from the lookup tables.

    Home > New Table

    Visitor Types =
DISTINCT (
    UNION (
        DISTINCT ( Gender[Visitor Type] ),
        DISTINCT ( Overall[Visitor Type] ),
        DISTINCT ( Platform[Visitor Type] )
    )
)

     and another new table

    Time Periods =
DISTINCT (
    UNION (
        DISTINCT ( Gender[Time Period] ),
        DISTINCT ( Overall[Time Period] ),
        DISTINCT ( Platform[Time Period] )
    )
)

     

     

4 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    6 years ago

    Hello Anonymous 

    You can create two lookup tables that contain the Visitor Types and Time Periods from all 3 tables.  Then you hook all 3 tables into the look tables and pull your slicers from the lookup tables.

    Home > New Table

    Visitor Types =
DISTINCT (
    UNION (
        DISTINCT ( Gender[Visitor Type] ),
        DISTINCT ( Overall[Visitor Type] ),
        DISTINCT ( Platform[Visitor Type] )
    )
)

     and another new table

    Time Periods =
DISTINCT (
    UNION (
        DISTINCT ( Gender[Time Period] ),
        DISTINCT ( Overall[Time Period] ),
        DISTINCT ( Platform[Time Period] )
    )
)

     

     

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        One more question, jdbuchanan71  - do you know how I can rank my time period (7 days through to 90 days)? I thought to add a column with 1,2,3,4 and then sort by that, but couldn't seem to enter the data... 

         

        Thanks again!