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
Highlighted
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
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

 

 

Highlighted
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
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

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!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors