Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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 CountryOrigin | Volume TO CountryOrigin |
FR-01 | DE-32 | 82 | |
DE-32 | FR-01 | 144 | |
DE-32 | FR-01 | 10 |
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?
Solved! Go to Solution.
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] )
)
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] )
)
User | Count |
---|---|
121 | |
69 | |
67 | |
57 | |
50 |
User | Count |
---|---|
176 | |
83 | |
69 | |
65 | |
54 |