cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper IV
Helper IV

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

Accepted Solutions
Highlighted
Community Champion
Community Champion

Re: Creating filter as measure

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

View solution in original post

1 REPLY 1
Highlighted
Community Champion
Community Champion

Re: Creating filter as measure

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

View solution in original post

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors