cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft
Microsoft

Re: Filter data using the value of a filtered table

@GeorgeK-VL,

 

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)

Capture.PNGCapture1.PNG

 

Regards,

Charlie Liao

 

View solution in original post

4 REPLIES 4
Highlighted
Resolver V
Resolver V

Re: Filter data using the value of a filtered table

Use FIRSTNONBLANK Function.

 

Total = CALCULATE(SUM('Word TimeSeries'[GSC_Combined_Impressions]), FIND(FIRSTNONBLANK(table[column], ""), 'Word Snapshot'[Word],1,0)>0)
Highlighted
Helper I
Helper I

Re: Filter data using the value of a filtered table

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

 

 

Microsoft
Microsoft

Re: Filter data using the value of a filtered table

@GeorgeK-VL,

 

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)

Capture.PNGCapture1.PNG

 

Regards,

Charlie Liao

 

View solution in original post

Highlighted
Helper I
Helper I

Re: Filter data using the value of a filtered table

@v-caliao-msft

Hello Charlie,

 

Thank you so much! This is exactly what I needed.

I need to start working more with variables.

 

Thank you!

Regards
George

 

 

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors