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

 

 

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
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

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

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors