Dynamic Chosen dimension with slicer

[MarkNL187]

Hi All, 

As per the visual below, I try to create a field in PowerBI that enables me to switch the X-axis to different dimensions (in this example just 2: switch to Region or Country).

Note that the PowerBI is connected to Azure Analysis services, so I can't use options like creating parameters.

What I've tried:

Step 1) Create a new table with 2 categories (Country and Region), which I can use as a slicer:  (SelectedDimension[Dimension])

2) Create a calculated column in the FactValue table that picks the correct column based on the slicer: 

if('SelectedDimension'[Dimension]="Country", 'DimSite'[SiteCountry],'DimSite'[SiteRegion])

 Unfortunately, this dax gives the error: A single value for column 'SiteCountry' in table 'DimSite' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

The DimSite table looks like:

I'm trying include the DAX calculations through Tabular Editor, happy to look for a solution I can write in DAX. If all fails I can always bring in the 'bookmark' solution

Thank you!

[lbendlin · Accepted solution]

Note that the PowerBI is connected to Azure Analysis services, so I can't use options like creating parameters.

Change the connection to DirectQuery with local model. Then you can create Field Parameters.