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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. 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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.