Can't filter table

[mossshvlw]

Hi all,

I'm trying to create a custom measure that simply filters my data:

myFilter =

FILTER (

'myTable',

[drug_type] == "METH"

)

I get the error: "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."  

Can anyone tell me what I'm doing wrong?  Is it possible to use filter in this way?

[mossshvlw]

Thanks all.  I've re-posted a better example of my question here https://community.powerbi.com/t5/Desktop/Help-changing-measures-using-slicers-to-filter-charts/td-p/...

Thanks for your assistance!

[parry2k]

@mossshvlw you can use this measure and put as visual level filter and put where value is not blank.

Or simply put a slicer or add a page level filter on drug type and choose "Meth" as value and it will filter the whole page. 

[littlemojopuppy]

Hi @mossshvlw 

A measure returns a single value, not a table.  Two options:

1. Create a table with exactly that DAX.  It will not be responsive to filtering, etc. because it will only be recalculated when data is refreshed or when the file is opened.
2. Create a table variable and then use the table variable to filter a measure.

DAX for option #2 would be something like this...

Measure =
VAR	myFilter =
	FILTER (
		'myTable',
		[drug_type] = "METH"
	)
RETURN

CALCULATE(
	SUM([Something]),
	myFilter
)

Hope this helps!  🙂

[aj1973]

Hi @mossshvlw 

No, Your measure is false. The formula FILTER needs to be embeded into another formula to filter an expression.

What do you want to do? Can you show us some examples?

[parry2k]

@mossshvlw filter return a table, you have to do some aggregation for example

myFilter =
COUNTROWS (
FILTER (
'myTable',
[drug_type] == "METH"
)
)

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[mossshvlw]

But what if I just want to filter the rows in the table?  And not do any calculations or aggregations?

[CNENFRNL]

@mossshvlw , a very fundamental concept is that a measure, at least up til now, returns a scalar value only.