Forum Discussion

jcs93's avatar
jcs93
Helper I
7 years ago
Solved

Calculated Column IF Statement using "IN" for multiple values

Looking for some assistance to figure out why I'm not getting what I'm expecting.

 

I'm building a calculated column using values selected within slicers.  It works great if only one value is selected and is throwing me a curve when no values are selected.

 

My code is something like this :

 

 
Criteria =
IF (
Table1[TextColumn]
IN {
IF (
NOT ( ISFILTERED ( Table2[TextColumn] ) ),
[TextColumnSelectedValue],
CONCATENATEX ( ALLSELECTED ( Table2[TextColumn] ), Table2[TextColumn] )
)
} ,"Meets", "Does Not Meet")
 
 
[TextColumnSelectedValue] is a measure using 'SELECTEDVALUES(Table2[TextColumn]) and is what appears to be breaking things when nothing is selected in the slicer.
 
The above code works perfectly any time one or more values are selected on the Table2[TextColumn] Slicer.
 
I've scoured the internet and can't find a similar situation for guidance.
 
 
 
 
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi jcs93 ,

     

    To create a measure as below.

     

    Criteria1 = 
var th = VALUES(TextHereTable[TextHere])
var mt = VALUES(MoreTextTable[MoreText])
RETURN
SWITCH(TRUE(),
    MAX(PowerBITest[TextHere]) in th
    && MAX(PowerBITest[MoreText]) in mt
    , "Meets Criteria", "Does Not Meet Criteria")

     

     

    Regards,

    Frank

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    First off, calculated columns are NOT recalculated when a slicer value changes.  They are ONLY recalculated when the table is refreshed.

     

    Do you mean you're creating a MEASURE that looks at slicer values?

    • jcs93's avatar
      jcs93
      Helper I
      7 years ago

      I'd disagree with your statement.  My column is recalculating when a slicer value is changed.  I have the product working on the desktop and in the web environment.

       

      When I say 'working,' I mean it's working when a value is selected.  when no value is selected it's acting weird.

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        jcs93 , do you mean you've created a column in a table VISUAL?.  If so, you've probably created a measure for this calculation.  Measures are re-evaluated whenever the filter context changes (ie, a slicer is changed, cross filtering from clicking on another visual, etc.).

         

        A calculated column in a table in your data model is different than a table visual that's created in a report tab.

         

        Screenshots would help the community solve your problem.  Can you provide some?

         

        This is an excerpt from SQLBI's article explaining the difference between Calculated Columns and Measures:

         

        Choosing between calculated columns and measures

Even if they look similar, there is a big difference between calculated columns and measures. 
        The value of a calculated column is computed during data refresh and uses the current row 
        as a context; it does not depend on user interaction in the report. 
        A measure operates on aggregations of data defined by the current context, 
        which depends on the filter applied in the report – 
        such as slicer, rows, and columns selection in a pivot table, or axes and 
        filters applied to a chart.