Slicer Value in Column Formula

[Anonymous]

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?

[v-shex-msft · Accepted solution]

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")

Regards,

Xiaoxin Sheng

[franciscocohen]

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")

[sumsha]

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.

[dduonghong]

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]

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

[Joerobert]

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

[rixmcx59]

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