- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Welcome to the Community
- Community Feedback
- Community Help Blog
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Cannot use "does not equal" in Filter function?

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

nobodyukno

Member

Cannot use "does not equal" in Filter function?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-18-2017
08:19 AM

I have a calculated column that I use to calculate the weighted average for my dataset. Oringially, in my query, I filtered out data in a column that had the amount of 0. I used the following column to caclulate my weighted average:

Weight = VAR IDS = Data[ID Number] RETURN CALCULATE(SUMX(Data,Data[Days Old]*Data[Total Amount Due]),FILTER('Data',Data[ID Number]=IDS))/SUM(Data[Days Old])

Then, I realized I wanted to actually include the data in the column that had an amount of zero for some other analysis I was doing. I adjusted my query to also include this data. This data would mess up the weighted average calculation I was doing because it should not be included in the calculation. My solution was to just add another filter to the formula to filter out the zero amounts just as I did in my query originally. That is when I received the error:

"A function 'FILTER' has been used in a True/False expression that is usead as a table filter expression. This is not allowed"

How can I achieve what I am looking to do? My formula that errored out is below:

Weight = VAR IDS = Data[ID Number] RETURN CALCULATE(SUMX(Data,Data[Days Old]*Data[Total Amount Due]),FILTER('Data',Data[ID Number]=IDS),FILTER('Data',Data[Total Amount Due]<>0)/SUM(Data[Days Old]))

Thanks.

3 REPLIES 3

malagari

Member

Re: Cannot use "does not equal" in Filter function?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-18-2017
08:55 AM

Your last FILTER argument is attempting to do FILTER(Data, [Total Amount Due] <> 0) and then dividing by the SUM function. What exactly are you trying to do here? Is the division by SUM supposed to be happening to the result of the CALCULATE? If so, you need to adjust your parentheses.

This is what your expression is currently saying, formatted:

Weight = VAR IDS = Data[ID Number] RETURN CALCULATE(

SUMX(

Data,

Data[Days Old] * Data[Total Amount Due]

),

FILTER('Data', Data[ID Number] = IDS),

FILTER('Data', Data[Total Amount Due] <> 0) / SUM(Data[Days Old])

)

Did you mean to do something like this at the end?

FILTER('Data', Data[Total Amount Due] <> 0)

) / SUM(Data[Days Old])

Dan Malagari

Consultant at Headspring

Consultant at Headspring

nobodyukno

Member

Re: Cannot use "does not equal" in Filter function?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-18-2017
09:07 AM

Thanks for the reply.

What I am trying to do is by row, multiply the Days Old column by the Total Amount Due column. Then divide this result by the sum of the entire Days Old column.

There is a column ID Number that should be unique per row, so this filter I guess is included to make sure it multiples only by each row (not sure if that is necessary).

And then finally I also now want to filter this calculation to not include any rows where the Total Amount Due column is zero. So if the Total Amount Due column is zero for a particular row, it should not multiply Days Old by Total Amount due and it should also not include this row in the sum of Days Old column which is what I am dividing by at the end.

Let me know if that doesn't make sense.

v-chuncz-msft

Community Support Team

Re: Cannot use "does not equal" in Filter function?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-19-2017
01:15 AM

If convenient, share us an example. Check if the following calculated column works.

Column = DIVIDE ( Data[Days Old] * Data[Total Amount Due], SUMX ( Data, Data[Days Old] * Data[Total Amount Due] ) )

Community Support Team _ Sam Zha

If this post**helps**, then please consider *Accept it as the solution* to help the other members find it more quickly.

If this post