cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mossshvlw
Helper II
Helper II

Can't filter table

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?
7 REPLIES 7
mossshvlw
Helper II
Helper II

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

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






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





littlemojopuppy
Super User
Super User

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

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?

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

parry2k
Super User
Super User

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






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





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

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


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is beyond their comprehension!

DAX is simple, but NOT EASY!

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it Out!

Join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors