Forum Discussion

Helper III

3 years ago

Solved

Filter Function across two tables

I have some dummy data in the two tables below.

I am trying to build measures with the Table 2 data but I want to filter the results to only include "banana" from Table 1. I am unable to get the filter function to work. These two tables would also be connected in my model.

The goal is to count in the RipeTest column and divide the 1s (numerator) with the total count (denominator) but ONLY the bananas.

dwel0120
3 years ago
I couldn't get it to work. I used KEEPFILTERS and I got it working.

4 Replies

jgeddes
Super User
3 years ago
If you are looking for a result like

I created the two tables you provided, without a relationship between them, and then wrote the following measure

Ripe Percentage =
var _ripeCount =
CALCULATE(
COUNT(ripeTable[RipeTest]),
TREATAS(VALUES(productTable[Product#]),ripeTable[Product #]),
FILTER(ripeTable, ripeTable[RipeTest] = 1)
)
var _productCount =
COUNT(productTable[Product])

return
DIVIDE(_ripeCount, _productCount, 0)

Hope this helps.
- dwel0120
  Helper III
  3 years ago
  jgeddes I need to filter out the bananas or the product in the measure because ultimately need a bar chart with a measure for each product.
jgeddes
Super User
3 years ago
Does this get you the result you are looking for?
Ripe Banana Percentage =
var _filteredProduct = "Banana"
var _ripeCount =
CALCULATE(
COUNT(ripeTable[RipeTest]),
TREATAS(VALUES(productTable[Product#]),ripeTable[Product #]),
FILTER(ripeTable, ripeTable[RipeTest] = 1),
FILTER(productTable, productTable[Product] = _filteredProduct)
)
var _productCount =
CALCULATE(
COUNT(productTable[Product]),
FILTER(productTable, productTable[Product] = _filteredProduct)
)
return
DIVIDE(_ripeCount, _productCount, 0)
- dwel0120
  Helper III
  3 years ago
  I couldn't get it to work. I used KEEPFILTERS and I got it working.