Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

Slicer Value in Column Formula

I'm wanting to create a column with the formula:

 

=IF(<value selected in slicer>=MyTable[MyColumn],<that value>,"Other Vendors")

 

MyColumn has static values, but over 8,000 different ones. Ideally, I'd like to be able to select multiple values in the slicer as well. My goal is a visual that shows the vendor(s) that I select compared to all of the vendors that are not selected in the slicer, lumping all of the other vendors together and showing them as one whole with no name. I just don't know how to reference the value(s) selected in the slicer in my column formula.

 

Any suggestions?

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

Hi @Anonymous,

 

Power bi not support dynamic calculate column based on slicer, you can use measure to instead the column.

 

Create a table to store value.

Selector Table = VALUES('sample'[Recommendation Id]) 

 

Add measure to Selector table to get selected value.

selected = IF(HASONEVALUE('Selector Table'[Recommendation Id]),VALUES('Selector Table'[Recommendation Id]),BLANK())

 

Add measure to original table to calculate based on slicer.

Check = 
IF(MAX('sample'[Recommendation Id])=[selected],[selected],"Other Vendors")

 

 

6.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

7 REPLIES 7
v-shex-msft
Community Support
Community Support

Hi @Anonymous,

 

Power bi not support dynamic calculate column based on slicer, you can use measure to instead the column.

 

Create a table to store value.

Selector Table = VALUES('sample'[Recommendation Id]) 

 

Add measure to Selector table to get selected value.

selected = IF(HASONEVALUE('Selector Table'[Recommendation Id]),VALUES('Selector Table'[Recommendation Id]),BLANK())

 

Add measure to original table to calculate based on slicer.

Check = 
IF(MAX('sample'[Recommendation Id])=[selected],[selected],"Other Vendors")

 

 

6.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Does this really works?

I tried to do the same and couldn't get any results TRUE on the 

Check = 
IF(MAX('sample'[Recommendation Id])=[selected],[selected],"Other Vendors")

This is populating the data only for MAX values of 'sample'[Recommendation Id] not updating based on actual value selected in slicer. Can anyone help me on this.

Hi,

Thank you so much for sharing this codes, it works perfect however I don't understand why we need to add the MAX function here (

MAX('sample'[Recommendation Id])

The condition should be compared for each sample[Recomendation Id] but not its MAX value. Thank you.

Anonymous
Not applicable

Thank you! I was searching for hours trying to figure this out. All the best 🙂

This is a good post showing how to dynamically manulating a dax formula by incorporating a slicer value as a measure.

I was hoping to revive this post, not sure how. I marked it as new hoping to get a little more detail. This is exactly what I want to do. I want to select a report year and report month using the slicer and then use the slicer value in a custom column to return records that meet a conditional criteria like

 

IF(StartYear)+(StartMonth)<=(Slicer#1ReportYear)+(Slicer#2ReportMonth) and (EndYear)+(EndMonth)>(Slicer#1ReportYear)+(Slicer#2ReportMonth) OR ISBLANK(EndDate),"In Progress","Started")

I realize the syntax in not correct, I just need to understand how to build the objects. I have a unrelated table for Year and one for Month and the main data source.

 

The issue I am trying to solve is counting submissions throughout their cycle, so if you have a submit date in Jan, I still nee to count it it Feb, Mar etc until it closes. I need to compare the dates to the reporting date.

 

Thanks for any help

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.