Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Filter data using the value of a filtered table

Hello everyone,

 

I am using a measure to find the total value of a column based on the value specified.

 

For example,

Total = CALCULATE(SUM('Word TimeSeries'[GSC_Combined_Impressions]), FIND("dahlia", 'Word Snapshot'[Word],1,0)>0)


However, I have 50 product categories for which I want to make the same calculation. So instead of creating 50 measures like the above, is it possible to use a table that will contain all the terms eg dahlia and when that table is filtered using a slicer to use the first value of the column as a value for the FIND formula?

 

So the measure might look like

 

Total = CALCULATE(SUM('Word TimeSeries'[GSC_Combined_Impressions]), FIND([FilteredColumnName], 'Word Snapshot'[Word],1,0)>0)

Any ideas on how this can be achieved?

Hope the above makes sense.

Thank you in advance

George

 

Need Help
  • v-caliao-msft's avatar
    v-caliao-msft
    8 years ago

    Anonymous,

     

    You can add product categories to a slicer, and then pass the selected item value to your FIND function.

    Measure =
    var selectedvalue = IF(HASONEFILTER('Product'[Product]),LASTNONBLANK('Product'[Product],1),BLANK())
    return CALCULATE(SUM(Sales[Sales]),FIND(selectedvalue,Sales[Product],1,0)>0)

     

    Regards,

    Charlie Liao

     

4 Replies

  • vik0810's avatar
    vik0810
    Icon for Resolver V rankResolver V
    8 years ago

    Use FIRSTNONBLANK Function.

     

    Total = CALCULATE(SUM('Word TimeSeries'[GSC_Combined_Impressions]), FIND(FIRSTNONBLANK(table[column], ""), 'Word Snapshot'[Word],1,0)>0)
    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hello vik0810

       

      Thank you for your reply.

       

      I tried this approach before, but unfortunately, it returns an error. The syntax is not correct because the FIRSTNONBLANK is used in a True/False expression that is used as a table filter expression and it is not allowed.

      Thank you for your prompt reply and really appreciate your help.

      Regards

      George

       

       

      • v-caliao-msft's avatar
        v-caliao-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        Anonymous,

         

        You can add product categories to a slicer, and then pass the selected item value to your FIND function.

        Measure =
        var selectedvalue = IF(HASONEFILTER('Product'[Product]),LASTNONBLANK('Product'[Product],1),BLANK())
        return CALCULATE(SUM(Sales[Sales]),FIND(selectedvalue,Sales[Product],1,0)>0)

         

        Regards,

        Charlie Liao