DAX Help: How to map intentional many-to-many relationship & double count some values on purpose?

[PBI12345]

Hi,

I am working with data and am struggling to return the output I intend, due to the presence of many-to-many relationships between my data.

Here is an example of a mapping table I have, noting that there are multiple values (India and Singapore) against an OD Country value of AustraliaSingapore:

Route	Origin Country	Destination Country	OD Country
Japan	Australia	Japan	AustraliaJapan
Singapore	Australia	Singapore	AustraliaSingapore
India	Singapore	India	SingaporeIndia
India	Australia	Singapore	AustraliaSingapore

Here is an example of my data, with "Route" hypothetically matched in on OD Country::

Record #	Origin Country	Destination Country	OD Country	Seats	Route
1	Australia	Japan	AustraliaJapan	50	Japan
2	India	Singapore	IndiaSingapore	70	India
3	Australia	Singapore	AustraliaSingapore	30	Singapore;India

However,  "Route" cannot be mapped in, because it maps to both Singapore and India. Annoyingly, I do infact want it to map against both of these conditions. Here is the output I want:

Route	Seats
Japan	50
India	100
Singapore	30

I am aware that this double counts Record # 3 as both "India" and "Singapore", but this is actually the result I am after.
Is there a DAX calculation I can make to force this to work this way?

Bonus question: What I really want to achieve with this data is to take the minimum value between two defined city pairs. So in this example, I want the minimum value between either AustraliaSingapore or IndiaSingapore; this is becuase the data deals with connecting flights, where the restraining factor is the flight with the minimum number of seats. I would then need to build this logic out across a large whitelist of city pairs, with anything not on the list just equalling its stated value.

In this case, the expected output would be:

Route	Seats
Japan	50
India	30
Singapore	30

I understand this is a huge longshot but any help would be TREMENDOUSLY appreciated -- this logic has been driving me absolutely crazy!

Thank you in advance

[Kedar_Pande]

@PBI12345 

Split the "Route" values in the "OD Country" column into individual country values.

Measure:

Route Seats = 
SUMX(
FILTER(
'YourDataTable', 
CONTAINSSTRING('YourDataTable'[OD Country], 'YourMappingTable'[OD Country])
),
'YourDataTable'[Seats]
)

First, create a mapping table for the city pairs:

Origin Country     Destination Country        Minimum Seats
Australia               Singapore                         (min seats)
India                     Singapore                         (min seats)

Then, use a DAX formula to find the minimum value:

Min Seats = 
MINX(
FILTER(
'YourDataTable', 
'YourDataTable'[Route] IN VALUES('YourMappingTable'[OD Country])
), 
'YourDataTable'[Seats]
)

💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[Poojara_D12]

Hi @PBI12345 

Can you please try the below steps:

Step 1: Double Counting Records

To achieve the result where a record is counted for both mapped "Route" values, you can use a calculated table to "flatten" the mappings in your data. This ensures each OD Country splits into multiple rows for each valid route.

Solution: Create a Calculated Table for Mapping

In Power BI, create a calculated table using DAX to expand the Route mappings for your OD Country.

ExpandedRoutes = 
ADDCOLUMNS(
    GENERATE(
        'MappingTable',
        VAR RoutesList = SUBSTITUTE('MappingTable'[Route], ";", ",")
        RETURN SELECTCOLUMNS(
            SPLIT(RoutesList, ","),
            "Route", TRIM([Value])
        )
    ),
    "OD Country", 'MappingTable'[OD Country]
)

This table will:

Split Route on ; into separate rows.

Add a new row for each route in the original mapping.

Example output for ExpandedRoutes:

Route OD Country

Japan

AustraliaJapan

Singapore

AustraliaSingapore

India

AustraliaSingapore

Step 2: Map Records to Routes

Once the mappings are expanded, we can create a new calculated table or measure to aggregate the Seats by Route.

Measure for Aggregation

Create a measure to calculate the total seats by each route:

Total Seats = 
CALCULATE(
    SUM('Data'[Seats]),
    TREATAS('ExpandedRoutes'[OD Country], 'Data'[OD Country]),
    TREATAS('ExpandedRoutes'[Route], 'MappingTable'[Route])
)

The TREATAS function ensures that OD Country and Route relationships are used for context filtering. The measure will give you the double-counted seat values:

Route Seats

Japan

50

India

100

Singapore

30

Step 3: Minimum Seats Between City Pairs

For your bonus requirement, you want to calculate the minimum value between city pairs for connecting flights. This requires additional logic to evaluate and filter data.

Whitelist of City Pairs
Create a table for the whitelist of city pairs in Power BI:

City Pair

AustraliaSingapore

IndiaSingapore

Measure for Minimum Seats
Use the following measure to calculate the minimum value between city pairs:

Min Seats = 
VAR WhitelistPairs = VALUES('Whitelist'[City Pair])
RETURN
SUMX(
    DISTINCT('ExpandedRoutes'[Route]),
    MINX(
        FILTER(
            'Data',
            'Data'[OD Country] IN WhitelistPairs
                && 'Data'[Route] = 'ExpandedRoutes'[Route]
        ),
        'Data'[Seats]
    )
)

This measure:

Iterates over each route.

Filters the data for city pairs in the whitelist.

Takes the minimum Seats value for each pair.

Final Output

Route Seats

Japan	50
India	30
Singapore	30

I think this should handle both your requirements effectively. Let me know if you need clarification or further adjustments!

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS 

[PBI12345]

Hi Poojara,

Thank you for the detailed this response. I have read the steps and this sounds like it will work.

However, I cannot get past your step 1 -- specifically, 'SPLIT' is not showing up as a valid DAX function and the whole piece of code errors. Am I doing something wrong? 

Thanks