How to dynamically change the column being used as a legend?

[PBI12345]

Hi,

I have a fact table which contain data about flights:

Marketing Carrier	Operating Carrier	Passengers
AA	AA	100
AA	BB	25
BB	BB	200

How can I create a system where, depending on a slicer selection ("Carrier Type": either Marketing Carrier or  Operating Carrier), the value being shown in a graph (for example, Sum[Passengers]) will be using either Marketing Carrier or  Operating Carrier as the legend?

Expected output if Carrier Type = Marketing Carrier:

AA Passengers = 125

BB Passengers = 200

Expected output if Carrier Type = Operating Carrier:

AA Passengers = 100

BB Passengers = 225

Furthermore, how can I then use this system in a bridge table? I have multiple fact tables that I would like to all toggle between Marketing or Operating, depending on the selection. 

I have thought about creating duplicate switch measures for every measure but this seems like an ineffective method. I've also tried a calculated column in the fact table that takes the value of Marketing Carrier or Operating Carrier, depending on the slicer selection, and that column would then be used in all calcs --  but that didn't work either. 

Any help would be grealy appreciated! 

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but one of ways to create this is by using field parameter feature in power bi.

Use report readers to change visuals (preview) - Power BI | Microsoft Learn

Please check the below picture and the attached pbix file (and the link above from Microsoft about how to create field parameter).

[DataNinja777 · Accepted solution]

Hi @PBI12345 

You can achieve this in Power BI by using a combination of a slicer table, a dynamic DAX measure, and a bridge table to unify carriers across multiple fact tables. First, create a CarrierType table that acts as the slicer for selecting between "Marketing Carrier" and "Operating Carrier":

CarrierType = DATATABLE("Carrier Type", STRING, { "Marketing Carrier", "Operating Carrier" })

Once you have this slicer, define a DAX measure that dynamically adjusts which carrier column is used in calculations. This measure will sum the passenger count based on the slicer selection:

Passengers by Carrier = 
VAR SelectedCarrierType = SELECTEDVALUE('CarrierType'[Carrier Type])
RETURN
    SWITCH(
        SelectedCarrierType,
        "Marketing Carrier", 
            CALCULATE(SUM(Flights[Passengers]), ALLEXCEPT(Flights, Flights[Marketing Carrier])),
        "Operating Carrier", 
            CALCULATE(SUM(Flights[Passengers]), ALLEXCEPT(Flights, Flights[Operating Carrier])),
        BLANK()
    )

If you want this logic to be used across multiple fact tables without duplicating measures, introduce a bridge table that consolidates unique carriers from both Marketing and Operating Carrier fields:

Carriers = DISTINCT(
    UNION(
        VALUES(Flights[Marketing Carrier]),
        VALUES(Flights[Operating Carrier])
    )
)

Next, establish two relationships: one between Carriers[Carrier] and Flights[Marketing Carrier] (inactive) and another between Carriers[Carrier] and Flights[Operating Carrier] (inactive). Instead of using calculated columns, define a DAX measure that dynamically applies the correct relationship based on the slicer selection:

Passengers by Selected Carrier = 
VAR SelectedCarrierType = SELECTEDVALUE('CarrierType'[Carrier Type])
RETURN
    SWITCH(
        SelectedCarrierType,
        "Marketing Carrier", 
            CALCULATE(SUM(Flights[Passengers]), USERELATIONSHIP(Carriers[Carrier], Flights[Marketing Carrier])),
        "Operating Carrier", 
            CALCULATE(SUM(Flights[Passengers]), USERELATIONSHIP(Carriers[Carrier], Flights[Operating Carrier])),
        BLANK()
    )

This approach ensures that all fact tables can dynamically switch between Marketing Carrier and Operating Carrier based on a single slicer selection, without requiring multiple versions of each measure. By leveraging USERELATIONSHIP within a bridge table, you can scale this system efficiently across multiple datasets while maintaining flexibility and avoiding redundant calculations.

Best regards,

[PBI12345]

Thanks DataNinja -- this approach worked well, and was ultimately what I needed given I already had a parameter passing a Top N value to a calculation.

Is there a way to get around having to create essentially two measures for each calculation? Ie. the first original calculation, and then the second measure which calculates the first based on the correct relationship? 

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but one of ways to create this is by using field parameter feature in power bi.

Use report readers to change visuals (preview) - Power BI | Microsoft Learn

Please check the below picture and the attached pbix file (and the link above from Microsoft about how to create field parameter).

[PBI12345]

Hi Jihwan,

Thank you for taking the time to create a .pbix file to show me a worked example.

Whilst your method did work (as per my instructions), I ultimately ended up using the other user's approach in my project due to a complication in my calcuations - I already am using a parameter to pass a "Top N" value, and this didn't interact correctly with the new parameter I built with your method.

Thanks again for help -- I will be using this approach in the future.