Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
vjnvinod
Impactful Individual
Impactful Individual

relationship setup

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

 

 

 

 

 

 

22 REPLIES 22
Mond
Helper III
Helper III

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.

 

vjnvinod
Impactful Individual
Impactful Individual

@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?

 

 

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.

vjnvinod
Impactful Individual
Impactful Individual

@Mond

 

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

Did you set up cross direction on the joins.

 

Have you reset the slicer (filter) once again

Phil_Seamark
Employee
Employee

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!

@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?

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!

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!

@Phil_Seamark @Mond

 

Hi thanks for the suggestion, but doesn't seems to be working, look at my scrubbed pbix file below.

https://www.dropbox.com/s/kaqbg9fc8h3wpo1/testchecknew.pbix?dl=0

 

My issues starts while i filter for a specfic value in the region, could be any region and i realised that tables/charts, doesn't interact properly.

 

However it works with my filter for Account sector.

 

Do let me know, if there is way i can fix this, i must be doing something incredibly wrong.

 

thanks for taking time and suggesting any change

Got the file, so if I select Africa from the region slicer. what is the issue?  I don't see any errors.


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

Proud to be a Datanaut!

 

@Phil_Seamark

 

Right, you may not find the issue while selecting the region as africa, what you need to do is select the region Africa and then click on Outbound to americas & the result is weird, below is what i see

Sector Revenue View is Blank (No value is getting displayed)

Account sector view is blank

Inbound matrix/table is Blank

 

thus making me feel that, its not interacting with other Matrix & charts,

 

attach is the screenshot.Region Africa.PNG

vjnvinod
Impactful Individual
Impactful Individual

@Phil_Seamark

 

hi

 

Any feedback ?

 

Regards, vinod

Hi @vjnvinod

 

It's been overnight here and I'm back at work now.  I will try to look at this later today.  I suspect we might need to code a bit of DAX to get the result you are after.


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

Proud to be a Datanaut!

@Phil_Seamark

 

thanks, i look forward

but i am still wondering, if you unfilter everything, this interaction works

 

But you filter for a particular region, it doesn't, is that some bug in power BI?

ideally when i filter for a particular region, and click on any outbound or inbound of that matters, that particular value should get displayed in my other visuals.

vjnvinod
Impactful Individual
Impactful Individual

@Phil_Seamark @Sean

 

Hi,

 

any feedback on this topic?

 

Appologise for chasing this out, just wanted to make sure we can do it, else we need to live with what is possible.

 

https://www.dropbox.com/s/kaqbg9fc8h3wpo1/testchecknew.pbix?dl=0

vjnvinod
Impactful Individual
Impactful Individual

@Phil_Seamark

 

Can you let me know if you are able to help with this Topic.?

i have approached some of Power BI experts, but no one has the way to do it.

HI @vjnvinod

 

This can be solved but will either mean some tricky DAX or duplicate tables.  I would like to help but have been quite busy the last 2 weeks.


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

Proud to be a Datanaut!

@Phil_Seamark

 

thanks Phil, it gives me some hope that it can be done.

will wait for your feedback.

 

Regards

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.