Reply
Highlighted
Member
Posts: 66
Registered: ‎04-21-2018

relationship setup

[ Edited ]

Hi,

 

how to easily setup the interaction between all of the visuals and charts. for instance i have 2 tables (Inbound and outbound) contains the same information.

 

I had to split this to 2 tables because of extracting the inbound revenue using Delivery region as filter and Outbound revenue using HQ region as filter.

Post that i  have to create a table which callls out the unique region from both the table and helps me interact with numbers.

 

now my major issues lies with when i filter for a particular region, my outbound values doesn't interact with pie charts, data matrix etc. look at the below relationship i have created, but it doesn't resolves the issue,

 

how do we make sure, that all my inbound datapoints are connnected to outbound?Relationshipsetup.PNG

 

 

 

 

 

 

Super User
Posts: 2,459
Registered: ‎11-29-2015

Re: relationship setup

isualsHi @vjnvinod

 

One thing to make sure you do is to use the HQRegion field from the Region table in your slicers and also on the axis of your visuals.  Go so far as to hide the HQRegion fields in both the Inbound and Outbound table.

 

That way, when a filter is being applied using the 'Region[HQRegion] field, it gets applied to rows in both Inbound and Outbound.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Regular Visitor
Posts: 22
Registered: ‎07-11-2017

Re: relationship setup

Create distinct list of HQ Regions from Inbound and Region table.

Join the new table with Inbound & Outbound.

create filter condition on the distinct region that was fetched from unique list.

 

Also make sure, you enable, cross filter direction on the join condition.

 

Member
Posts: 66
Registered: ‎04-21-2018

Re: relationship setup

@Mond

 

my table is inbound and Outbound, so i have

Created distinct list of HQ Regions from Inbound and Outbound table.

Joined the new table with Inbound & Outbound (as you see in my screenshot)

 

create filter condition on the distinct region that was fetched from unique list? what you mean by this, can you help?

 

 

Regular Visitor
Posts: 22
Registered: ‎07-11-2017

Re: relationship setup

create filter condition on the distinct region that was fetched from unique list? what you mean by this, can you help?

I meant to use this distinct region in your slicer (visual) for filter purposes.

Member
Posts: 66
Registered: ‎04-21-2018

Re: relationship setup

@Phil_Seamark

 

I have used the below DAX for creating a newtable as region

Region =
FILTER(
DISTINCT(
UNION(
DISTINCT(Inbound[HQRegion]),
DISTINCT(Inbound[DeliveryRegion])
)
),
NOT(ISBLANK(Inbound[HQRegion])))

 

 

Go so far as to hide the HQRegion fields in both the Inbound and Outbound table? Not sure why i have to hide this?

Member
Posts: 66
Registered: ‎04-21-2018

Re: relationship setup

@Mond

 

its already there as a dropdown, but my outbound values are not interacting with my other tables

Super User
Posts: 2,459
Registered: ‎11-29-2015

Re: relationship setup

Hi @vjnvinod

 

I only suggested you hide those fields so you don't accidentally use them in your slicer, or on an axis. 

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Regular Visitor
Posts: 22
Registered: ‎07-11-2017

Re: relationship setup

Did you set up cross direction on the joins.

 

Have you reset the slicer (filter) once again

Super User
Posts: 2,459
Registered: ‎11-29-2015

Re: relationship setup

Just to be clear.  Only use fields from the middle tables (surrounded by the red rectangle) for your slicers and on the axis of your visuals.

 

Do not use ANY fields from the two tables with the red crosses in a slicer or on the axis of a visual.  You can use numeric fields from these tables for measures etc.

 

 

 

image.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!