Forum Discussion
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?
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
7 Replies
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
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
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.
