How to create a dimension by selecting two dimensions?

[EugenioProlog]

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 combines two dimensions selected by the user. The user will have two parameters to choose dimension1 and dimension2 to combine. 

- the other is my total_sales. 

how can I do this aggregated dimension allowing the user to select the 2 dimensions to combine?

[EugenioProlog]

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?

[v-bmanikante]

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

[bhanu_gautam · Accepted solution]

@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.