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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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