FILTER ALL Syntax Troubles

Question

Im trying to clear the filter applied by a visual on another table and cant figure out where I am going wrong. My formula is:

Measure 2 = FILTER(ALL(TableName),IF([ColumnName]=0 && [ColumnName2]="Yes",[EmployeeName]))

I keep getting the error 'The expression refers to multiple columns. Multiple columns cannot be converted to scalar value."

Any direction as to where I am going wrong?

mfelix · Answer

Hi&nbsp;CEllinger&nbsp;,
&nbsp;
What is the purpose of your measure?
&nbsp;
Do you want to returnthe Employee Name if the two columns refer to the parameters you have?
&nbsp;
Beloieve that&nbsp; the problem is that filter function returns a table and you are trying to assign it to a scalar value (a measure). So the result is not possible.
&nbsp;
The FILTER syntax should be used as last parameter of a CALCULATE or as part of a group by of a table, and then apply a summarization to those values.
&nbsp;
Your measure should be something like:
Measure 2 = CALCULATE( IF([ColumnName]=0 &amp;&amp; [ColumnName2]="Yes",[EmployeeName]);FILTER(ALL(TableName))
However without any data is difficul to give you an answer can you share some sample data and expected result?
&nbsp;
Regards,
MFelix

cellinger · Answer

MFelix&nbsp;, I am trying a different tack to solve a problem that has been vexing me for a while. Right now, I have a dynamic visual that changes two tables in the exact same way. Im trying to figure out how exactly the filter context works to see if I can use the same dynamic visual to produce multiple distinct tables.&nbsp;&nbsp;Video of fields changing as I click on dates.&nbsp;I have the same fields populating the tables. When I click a date, I want one table to show the responses filtered by visual and other table to show the table I was describing in my OP.&nbsp;Here is a link to the data I used.&nbsp;I have attached pictures of everything else below here. If there is anything else you might need from me, please, let me know. Im driving myself crazy trying to figure this out. Im sure its going to be simple, too.&nbsp;Thanks!This is my data modelThis is the custom visual that I am using

mfelix · Answer

Hi&nbsp;CEllinger&nbsp;,
&nbsp;
Can you give me a little bit more information about the result you want to achieve please.
&nbsp;
When you refer "When I click a date, I want one table to show the responses filtered by visual and other table to show the table I was describing in my OP" whatis exactly the final result you want to achieve? 
&nbsp;
Based on the tables you submitted can you share a mockup of the result?
&nbsp;
Sorry for the late response.
&nbsp;
Regards,
MFelix

cellinger · Answer

MFelix&nbsp;, dont apologize for a late response. I appreciate the help regardless of the timeline!&nbsp;So, to give you the entire picture:&nbsp;I have a Calendar Slicer Visual that whose measure isApproved Request = CALCULATE(DISTINCTCOUNT('Full Time Off'[Emp_ID]),'Full Time Off'[Approved]=8)The Calendar Slicer Visual shows a count of Employees that are off on a selected day.&nbsp;&nbsp;Date OffEmployee4/2/2019Idalia Woolston &nbsp;4/2/2019Sofia Sevy &nbsp;4/3/2019Idalia Woolston &nbsp;4/3/2019Sofia Sevy &nbsp;4/4/2019Idalia Woolston &nbsp;4/4/2019Sofia Sevy &nbsp;4/4/2019Ray Medders &nbsp;4/5/2019Idalia Woolston&nbsp;&nbsp;I want to see all of the Employees that will be off on a selected date. If I drop my Employee's Names into a table (Table 1) on my report, selecting a single day will list all of those Employees counted by the Measure.e.g. If I click on 4/4/2019, I get&nbsp;Idalia Woolston &nbsp;Sofia Sevy &nbsp;Ray Medders &nbsp;&nbsp;I want to be able to create a table (Table 2) with the exact same data but that shows all of those Employees NOT identified by the initial measure.&nbsp;Original Employees&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Table 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Table 2Selene Styons &nbsp;Idalia Woolston &nbsp;Selene Styons &nbsp;Idalia Woolston &nbsp;Sofia Sevy &nbsp;Wynona Mailhot &nbsp;Wynona Mailhot &nbsp;Ray Medders &nbsp;Leana Fleeman &nbsp;Leana Fleeman &nbsp;&nbsp;Nathanael Hough &nbsp;Nathanael Hough &nbsp;&nbsp;Nereida Cardamone &nbsp;Sofia Sevy &nbsp;&nbsp;Nickolas Linquist &nbsp;Nereida Cardamone &nbsp;&nbsp;Danae Pon &nbsp;Nickolas Linquist &nbsp;&nbsp;Mervin Mcgilvray &nbsp;Danae Pon &nbsp;&nbsp;Jessenia Trapp &nbsp;Mervin Mcgilvray &nbsp;&nbsp;Hilton Soule &nbsp;Ray Medders &nbsp;&nbsp;Alesha Griffeth &nbsp;Jessenia Trapp &nbsp;&nbsp;Emil Rachel &nbsp;Hilton Soule &nbsp;&nbsp;Beatriz Kessinger &nbsp;Alesha Griffeth &nbsp;&nbsp;Agnes Gum &nbsp;Emil Rachel &nbsp;&nbsp;Clarita Rockmore &nbsp;Beatriz Kessinger &nbsp;&nbsp;Leslee Lengyel &nbsp;Agnes Gum &nbsp;&nbsp;Mac Napper &nbsp;Clarita Rockmore &nbsp;&nbsp;&nbsp;Leslee Lengyel &nbsp;&nbsp;&nbsp;Mac Napper &nbsp;&nbsp;&nbsp;&nbsp;Does that make more sense? Also, thank you so much for all of your time already.

Forum Discussion

FILTER ALL Syntax Troubles

4 Replies

Related content

Data Ingestion Troubles

Syntax problem

SQL Query Syntax issue

SQL syntax error

Trouble scraping SharePoint Version History