Cannot Use Text String with Comparison Operator as Input to Filter()

Question

I am trying to use selected items from a slicer as the input to a filter statement. To get the slicer selections, I am building up a text string, which has a comparison operator. The chart that I am using this in, then gives an error, 'Calculation error in measure ... of type text to type true/false'.

I have a measure that I created to count the number of items opened & closed within a given timeframe. I have a date slicer, from which I am successfully pulling the start/stop dates and using those as inputs to my Filter statement, and counting the rows returned.

I am now trying to add a name selection from a slicer to the Filter statement and not having much luck.

I am pulling the names selected from a slicer with the following code.

Names = CONCATENATEX(
ALLSELECTED(Table[Column]),
"Table[Column]="&Table[Column],
"  || ")

This gives me a text string like, 'Table[Column]="John Smith" || Table[column] "Jane Smith"

When I put this into my filter statement

Filter(Working Filters && (Names))

That's when I get my error. If I type the statement in directly, it works. But that doesn't help me, since I will not know what is selected

Filter(Working Filters && (Table[Column]="John Smith" || Table[column] "Jane Smith")

How can I use my text String as the input to the filter? Or is there a different way that i could get my slicer selections into filter?

What is your data model? Feels to me Power bi should give you this functionality already but you might miss a relation? — Aldert
In essence, this is an issue tracking, metrics, report. I am trying to show how many items were opened and closed in a given week/month/quarter, by using the detected /closing date to determine how many items were opened/Closed. I am trying to add in the person(s) who logged the item in the calculation of the measure. — user6767022
Please make a snapshot of the data model with the tables you have. — Aldert

Alexis Olson Alexis Olson · Accepted Answer · 2019-05-30T15:20:44

The FILTER function doesn't take text arguments like that. To read values from a slicer, you can use SELECTEDVALUE (for a single value) or VALUES (for possibly multiple values). However, most of the time, if your tables and relationships are set up correctly, you shouldn't have to do that manually unless you are using a slicer to set a parameter rather than as a filter.

Let's say you have Table1 and Table2 that are not related and you are using Table2[Column2] as a slicer. Then you could write a filter like this:

FILTER( Table1, Table1[Column1] IN VALUES( Table2[Column2] ) )

Thus even though Table1 and Table2 are not related, you are only allowing values in Table1[Column1] that match the values you've selected with your Table2[Column2] slicer.

Cannot Use Text String with Comparison Operator as Input to Filter()

1 Answers