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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors