Relationships between two columns

[Anonymous]

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

[jdbuchanan71 · Accepted solution]

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]

Amazing, thank you so much, @jdbuchanan71 

[Anonymous]

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!

[jdbuchanan71]

You can add another column to the table in the same step as when you create it.

Time Periods = 
ADDCOLUMNS(
    DISTINCT (
        UNION (
            DISTINCT ( Gender[Time Period] ),
            DISTINCT ( Overall[Time Period] ),
            DISTINCT ( Platform[Time Period] )
        )
    ),
    "Time Period Sort",
    SWITCH( 
        [Time Period],
        "Last 7 days",1,
        "Last 30 days",2,
        "Last 90 days",3,
        99
    )
)