Power BI does not support selecting between two columns at row level inside a measure

[Tanisha10]

Hi All,

Based on the Industry Code Type selection, Industry code should switch between 2 columns and display the filtered records.

I have a slicer table like below:

Industry Code Type = DATATABLE("Values", STRING, {{"NAICS"}, {"SIC"}})

Created a measure:

Industry Code =
SWITCH (SELECTEDVALUE('Industry Code Type'[Values]),    
"SIC",  'Account'[Account_SIC_Code],    
"NAICS", 'Account'[Account_NAICS Code])

I just wanted to display filtered SIC/NAICS code values in the second slicer based on the selection in the first slicer.
But as per my knowledge, 
>Measures are evaluated at runtime, but cannot be used to dynamically switch between columns per row. Power BI does not support selecting between two columns at row level inside a measure. 
>Calculated columns are evaluated at data refresh, not interactively.
>Slicers are filters applied at visual level, not to control formula logic inside columns.

Is there a way to switch between 2 columns inside a slicer filter? 

Best Regards,

Tanisha

[v-sdhruv]

Hi @Tanisha10 ,
Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[v-sdhruv]

Hi @Tanisha10 ,
Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[v-sdhruv]

Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[v-sdhruv · Accepted solution]

Hi @Tanisha10 ,

Power BI does not support row-level dynamic column switching inside a measure or calculated column based on slicer selection because of how the data model is evaluated:

Calculated columns are evaluated at refresh time, not during report interaction.

Measures are dynamic but cannot return column values per row — only aggregations (like max, Sum, etc.).

Slicers filter data but cannot control what column is used row-by-row in a visual.
But you can try using field Parameters.

Power BI now supports Field Parameters, which is the right way to dynamically switch between columns in slicers or visuals interactively at runtime.

1. Enable Field Parameters:

   • Go to Modeling > New Parameter > Fields.

2. Create a Field Parameter with SIC and NAICS columns:

   • Name it: Industry Control field

   • Add:

     • 'Account'[Account_SIC]

     • 'Account'[Account NAICS]

Hope this helps!
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You!

[bhanu_gautam]

@Tanisha10 Add a calculated column in your 'Account' table that combines both SIC and NAICS codes into a single column based on the selection:

DAX
Combined_Industry_Code =
IF (
SELECTEDVALUE('Industry Code Type'[Values]) = "SIC",
'Account'[Account_SIC_Code],
'Account'[Account_NAICS_Code]
)

Create a measure to display the selected industry code based on the slicer selection:

DAX
Selected_Industry_Code =
SWITCH (
SELECTEDVALUE('Industry Code Type'[Values]),
"SIC", MAX('Account'[Account_SIC_Code]),
"NAICS", MAX('Account'[Account_NAICS_Code])
)

Use the Selected_Industry_Code measure in your visuals to dynamically display the filtered SIC/NAICS code values based on the slicer selection.

Add a slicer to your report using the Combined_Industry_Code column. This slicer will dynamically update based on the selection in the Industry Code Type slicer.

[Tanisha10]

Hi @bhanu_gautam , Thank you so much for your quick response.
I also tried out adding a calculated column in 'Account' table earlier that combines both SIC and NAICS codes into a single column based on the selection using the same DAX that you mentioned. But it stores only values of the first Type i.e. NAICS code (by default), not the other one (SIC code). Basically Calculated columns can not be evaluated on the fly , it is always at data refresh time.

DAX:
Combined_Industry_Code =
IF (
SELECTEDVALUE('Industry Code Type'[Values]) = "SIC",
'Account'[Account_SIC_Code],
'Account'[Account_NAICS_Code]
)

Thanks,

Tanisha