Forum Discussion

EugenioProlog's avatar
EugenioProlog
Icon for Helper III rankHelper III
1 year ago
Solved

How to create a dimension by selecting two dimensions?

I have a table with 4 dimensions (age, gender, country, payment_method). I have a measure with total_sales.    I want to create a table with 2 columns:   - one is a "aggregated dimension" that co...
Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    EugenioProlog In Power BI, go to the "Modeling" tab and select "New Parameter."
    Create two parameters, Dimension1 and Dimension2, each containing the names of the dimensions you want to combine (e.g., "age", "gender", "country", "payment_method").

     

    Use DAX to create a calculated column that combines the two selected dimensions. You can use the SWITCH function to dynamically select the dimensions based on the parameters.

    AggregatedDimension =
    SWITCH(
    TRUE(),
    'Table'[Dimension1] = "age" && 'Table'[Dimension2] = "gender", 'Table'[age] & " - " & 'Table'[gender],
    'Table'[Dimension1] = "age" && 'Table'[Dimension2] = "country", 'Table'[age] & " - " & 'Table'[country],
    'Table'[Dimension1] = "age" && 'Table'[Dimension2] = "payment_method", 'Table'[age] & " - " & 'Table'[payment_method],
    'Table'[Dimension1] = "gender" && 'Table'[Dimension2] = "country", 'Table'[gender] & " - " & 'Table'[country],
    'Table'[Dimension1] = "gender" && 'Table'[Dimension2] = "payment_method", 'Table'[gender] & " - " & 'Table'[payment_method],
    'Table'[Dimension1] = "country" && 'Table'[Dimension2] = "payment_method", 'Table'[country] & " - " & 'Table'[payment_method],
    'Table'[Dimension1] = "gender" && 'Table'[Dimension2] = "age", 'Table'[gender] & " - " & 'Table'[age],
    'Table'[Dimension1] = "country" && 'Table'[Dimension2] = "age", 'Table'[country] & " - " & 'Table'[age],
    'Table'[Dimension1] = "payment_method" && 'Table'[Dimension2] = "age", 'Table'[payment_method] & " - " & 'Table'[age],
    'Table'[Dimension1] = "country" && 'Table'[Dimension2] = "gender", 'Table'[country] & " - " & 'Table'[gender],
    'Table'[Dimension1] = "payment_method" && 'Table'[Dimension2] = "gender", 'Table'[payment_method] & " - " & 'Table'[gender],
    'Table'[Dimension1] = "payment_method" && 'Table'[Dimension2] = "country", 'Table'[payment_method] & " - " & 'Table'[country]
    )

     

    Add a new table visual to your report.
    Drag the AggregatedDimension calculated column to the table.
    Drag the total_sales measure to the table.

    Ensure that the parameters Dimension1 and Dimension2 are set up as slicers or dropdowns in your report so that users can select the dimensions they want to combine.

EugenioProlog's avatar
EugenioProlog
Icon for Helper III rankHelper III
1 year ago

What i have 10 or 20 different dimension columns? This DAX formula grows exponentialy! Is there a simpler solution where my DAX formula dinamically grows with the number of dimensions that I have?

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi EugenioProlog ,

 

Thank you for reaching out to Microsoft Fabric Community Forum.

bhanu_gautam Thank you for your quick response.

 

There is no direct option in Power BI to dynamically select and combine values from two different slicers into a single selection or output. . As mentioned by a Super User, you can go with that approach, which somewhat helps your scenario,especially if you're trying to simulate dynamic behavior using techniques like leveraging field parameters in visuals.

 

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Please don't forget to give a "Kudos " – I’d truly appreciate it!

 

Regards,

B Manikanteswara Reddy