cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
leo_89
Post Prodigy
Post Prodigy

Creating filter as measure

Hi there,

 

I have a fact table which contains data about the sales such as what was sold, how much sold, when sold, who sold, how many customers bought particular sold, so on and so forth.  You can call that fact table contains raw data. Now I want to apply some exclusion rules on the data (exclude some data based on some rules). For example:

1) exclude sale information about product that few customers (i.e. 5) have bought. 

2) exclude sale information about the product that was sold 'online'.

 

Initially, I thought to create two separate filters containing each rule and then apply to fact table of sales. So, I created a measure something like the following:

filter 1 =  

FILTER('Sales Information','Sales Information'[NumberOfConsumers] > 5)
 
When I tried to create it, it throws an error "The expression refers to multiple columns, Multiple columns can not be converted to a scalar value". 
I converted the measure into table. Now I have a separate table which is created as the result of running the above command. Then I added the second exclusion rule by using 'and' operator in filter function.  Now, I have about 8 to 9 exclusion rules. I dont want to use 'and' operator in filter function. 
 
My question is there a way I could use filter function as measure? Can I use multiple filter function that combined returned the table?

 

 

 

1 ACCEPTED SOLUTION
mahoneypat
Super User
Super User

Your error is that you can't return a table as a scalar value.  You have to aggregate it first.  The most common way is to wrap your FILTER in a COUNTROWS ( FILTER(Table, Table[Column] >5))

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

1 REPLY 1
mahoneypat
Super User
Super User

Your error is that you can't return a table as a scalar value.  You have to aggregate it first.  The most common way is to wrap your FILTER in a COUNTROWS ( FILTER(Table, Table[Column] >5))

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.