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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Vigul
Regular Visitor

Invert 2 Filters

Hello everyone, I`d like to ask for help with 1 interesting issue:

We have following table (for example)

CountryOrigin (Data Type=Text)CountryDestination (Data Type=Text)Volume FROM CountryOriginVolume TO CountryOrigin
FR-01DE-3282 
DE-32FR-01144 
DE-32FR-0110 


We have 2 slicers where we select point for CountryOrigin and CountryDestination. When we select FR-01 in CountryOrigin we have DE-32 and FR-45 on CountryDestination displayed and we select DE-32. We get info that we have 82 cars going from FR-01 to DE-32.

The issue is, we want also to get data (during same selection) on how many Trucks we have FROM DE-32 back to FR-01. Meaning that when we select FR-01>>>DE-32 we get Volume FROM CountryOrigin 82 and also second column with Volume TO CountryOrigin 154 (144+10) displayed.

 

We tried doing measure like this:
Volume from-TO =
VAR FromTo =
FILTER('Table', 'Table'[CountryOrigin]=SELECTEDVALUE('Table'[CountryDestination]) && 'Table'[CountryOrigin]=SELECTEDVALUE('Table'[CountryOrigin]))
RETURN
CALCULATE(SUM('Table'[Volume FROM CountryOrigin]), FromTo)


But this measure results in empty values. Also we don`t have access to Queryeditor so it`s not possible to do anything from it.

 

Does anyone have any ideas how we can do this inverted selection, or is it even possible without query editing? 

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

You could create 2 new dimension tables for use on the slicers, e.g.

From Slicer =
SELECTCOLUMNS(
	DISTINCT(
		UNION(
			DISTINCT( 'Table'[CountryDestination] ),
			DISTINCT( 'Table'[CountryOrigin] )
		)
	),
	"Country", 'Table'[CountryDestination]
)

To Slicer =
SELECTCOLUMNS(
	DISTINCT(
		UNION(
			DISTINCT( 'Table'[CountryDestination] ),
			DISTINCT( 'Table'[CountryOrigin] )
		)
	),
	"Country", 'Table'[CountryDestination]
)

Create an active relationship from each table to the appropriate column on your fact table. Then create an inactive relationship from each table to the other column. You can then create a measure like

Volume to country origin =
CALCULATE (
    SUM ( 'Table'[Volume from country origin] ),
    USERELATIONSHIP ( 'Table'[Country origin], 'To slicer'[Country] ),
    USERELATIONSHIP ( 'Table'[Country destination], 'To slicer'[Country] )
)

View solution in original post

1 REPLY 1
johnt75
Super User
Super User

You could create 2 new dimension tables for use on the slicers, e.g.

From Slicer =
SELECTCOLUMNS(
	DISTINCT(
		UNION(
			DISTINCT( 'Table'[CountryDestination] ),
			DISTINCT( 'Table'[CountryOrigin] )
		)
	),
	"Country", 'Table'[CountryDestination]
)

To Slicer =
SELECTCOLUMNS(
	DISTINCT(
		UNION(
			DISTINCT( 'Table'[CountryDestination] ),
			DISTINCT( 'Table'[CountryOrigin] )
		)
	),
	"Country", 'Table'[CountryDestination]
)

Create an active relationship from each table to the appropriate column on your fact table. Then create an inactive relationship from each table to the other column. You can then create a measure like

Volume to country origin =
CALCULATE (
    SUM ( 'Table'[Volume from country origin] ),
    USERELATIONSHIP ( 'Table'[Country origin], 'To slicer'[Country] ),
    USERELATIONSHIP ( 'Table'[Country destination], 'To slicer'[Country] )
)

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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