Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

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. Idea...
  • Anonymous's avatar
    Anonymous
    9 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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

Joerobert's avatar
Joerobert
Advocate V
8 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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