Need help with Dynamic Fields selection based on the no of values selected in the Slicer.

[wickkey]

Hi Everyone,

I have a column chart in Power BI which shows statewise sales. I have a slicer for states. The requirement is, if the user selects only one state in the slicer, the table should show citywise sales for that respective state. If the user selects more than one state in the slicer, I want to show statewise sales.

 

I tried using the following DAX formula:

Column = IF(COUNTROWS(VALUES(Orders[State])) = 1, VALUES(Orders[City]), VALUES(Orders[State]))

However, I'm getting an error message:

MdxScript(Model) (4, 76) Calculation error in measure 'DAX Measures'[Column]: A table of multiple values was supplied where a single value was expected.

Can someone please help me with this? Any help would be appreciated.

Thanks!

[wickkey]

I really appreciate your help. Your response was exactly what I needed. Thank you for taking the time to help me out! 😊

[OwenAuger · Accepted solution]

Hi @wickkey 

I've attached an example of how this can be done. It's an interesting application of field parameters.

Field parameters are a preview feature so need to be enabled in the "Preview features" settings.

1. Create a Field Parameter for State and City columns. I called this State/City.
2. Place this Field Parameter on the axis of the visual.
3. Create a measure called State/City Filter.
   The purpose of this measure is to filter State/City by returning 1 for the combinations we want to display (if a single state is selected and State/City = "City", or multiple states selected and State/City = "State") or 0 otherwise.
4. Add the State/City column as a visual-level filter to the visual, and select Filter type = Top N. Show Top 1 items with By value set to the State/City Filter measure. Click Apply filter.
5. Now, when a single state is selected on the slicer, the visual axis will show City, and when multiple states are selected, the visual axis will show State.

Does this work in your report?

State/City Filter = 
-- SingleState = 1 if a single state has been selected on slicer, otherwise 0
VAR SingleState =
    INT (
        CALCULATE ( HASONEVALUE ( Orders[State] ), ALLSELECTED () )
    )
-- CityOption = 1 if the current Field Parameter option = City, otherwise 0
VAR CityOption =
    INT ( MIN ( 'State/City'[State/City] ) = "City" ) 
RETURN
    -- Return 1 if SingleState & CityOption both = 1 (display city), or if SingleState & CityOption both = 0 (display state)
    INT ( SingleState = CityOption )