How to Reference Slicer Values in Column Formula

Are you curious to know if you can reference slicer values in a column formula? Or have you ever wondered how to do it?

In Power BI, you can reference the selected value(s) in a slicer in a column formula using DAX formulas.

In this article, I will explain a step-by-step guide on how to reference a slicer value in a column formula.

Table of Contents show

Getting started: How to Reference Slicer Values in Column Formula

The first thing to do is to import your data and create a slicer in the visualization pane. To reference these slicer values in a column formula, you can follow the below guidelines

Single selected value

To reference a single selected value in the slicer, you can use the SELECTEDVALUE function. The formula of the function is:

SELECTEDVALUE(Slicer_Name[Column_Name])

Where Slicer_Name is the name of the slicer and Column_Name is the name of the column in the slicer that you want to reference.

For example, if you have a slicer named Region_Slicer with a column named Region, you can create a calculated column in your data table that returns a specific value based on the selected region as follows:

Selected_Region =IF(SELECTEDVALUE(Region_Slicer[Region]) = "North", "North Value", 
          IF(SELECTEDVALUE(Region_Slicer[Region]) = "South", "South Value", 
         IF(SELECTEDVALUE(Region_Slicer[Region]) = "East", "East Value", 
           IF(SELECTEDVALUE(Region_Slicer[Region]) = "West", "West Value", "None"))))

This formula checks the selected value of the slicer Region_Slicer[Region] and returns one of the following strings based on the selected value: North Value, South Value, East Value, West Value, or None.

Multiple selected values

In the case of multiple selected values, you can use the CALCULATE function to reference all selected values in the slicer and perform a calculation based on the filtered data. The syntax of the function is:

CALCULATE(SUM(Table_Name[Column_Name]), Slicer_Name)

Table_Name is the name of the table that contains the column you want to reference. Column_Name is the name of the column, and Slicer_Name is the name of the slicer.

For example, if you have a table named Sales with a column named Amount and a slicer named Slicer_Name with a column named Region, you can create a measure that returns the sum of sales amounts based on the selected regions as follows:

Total Sales:=CALCULATE(SUM(Sales[Amount]), Slicer_Name)

FAQs

What is a slicer in Power BI?

A slicer in Power BI is a visual filter that allows you to select a subset of data to view in your report.

What happens if no value is selected in a slicer?

If no value is selected in a slicer, the DAX functions will return an error.

Conclusion: How to Reference Slicer Values in Column Formula

With the above functions, you can reference the selected value(s) in a slicer in a column formula in Power BI and create calculated columns. And with them, you can build visuals.

NB: These formulas are a bit technical, hence, you should pay careful attention not to make mistakes when writing them.

I hope you understood these steps.

Thanks for reading.