Multiple values from same column under same visual

[saud968]

I want to showcase two columns in one visual by taking specific count values. From the CSAT column in the visual one column will show a count of 4, 5 for a PCM and the other will show 1,2,3. 

I have tried this C-SAT = CALCULATE(COUNTROWS(CSAT), FILTER(CSAT, CSAT[CSAT] = 4 && 5 )) and C-SAT = CALCULATE(COUNT(CSAT), FILTER(CSAT, CSAT[CSAT] = 4 && 5 )) but it is now working it is not giving the correct count

Case Number	Responded Date (America/Cancun)	Closed Date (America/Cancun)	CSAT	CES	Comments	PCM	Product	Inquiry/Problem/Request
154247	7/1/2021 1:30 AM	7/1/2021 1:29 AM	5	5		Saud Ansari	Fortify	Problem
121032	1/30/2021 4:08 AM	1/22/2021 5:33 PM	5	5		Salman Patel	Portal	Inquiry
121371	2/2/2021 4:04 PM	1/26/2021 4:01 PM	3	3	The feature is not available right now in the portal	Salman Khan	Security - Software	Problem
120790	1/31/2021 4:41 PM	1/31/2021 2:08 AM	4	4	While the Partner care team were very good at opening a line of communication, we still feel that the NOC are often letting us down.	Ronak Waghela	Portal	Problem
135328	4/30/2021 10:56 AM	4/28/2021 2:03 AM	5	5		Saud Ansari	HD Service	Inquiry
154803	6/30/2021 12:58 PM	6/30/2021 12:56 PM	5	5		Salman Khan	Control	Inquiry
162411	8/3/2021 11:09 AM	7/30/2021 8:16 PM	5	5		Ronak Waghela	Assist Help Desk	Problem
123590	2/18/2021 4:14 PM	2/10/2021 5:15 PM	1	1	I doubt there is anything connectwise can do to stop sucking. We really HATE that we have to deal with Connectwise as a Continuum	Salman Patel	Portal	Inquiry
134973	4/26/2021 4:03 PM	4/26/2021 1:51 PM	5	5		Salman Patel	Portal	Inquiry
156644	7/20/2021 4:22 PM	7/13/2021 3:54 PM	3	3		Ronak Waghela	Portal	Inquiry
136168	5/5/2021 9:51 AM	5/4/2021 4:57 PM	4	4	N/A - I was the last person to be assigned this. I'm sorry I cannot provide further feedback. I appreciate your support while looking into this case.	Salman Khan	Not Product Related	Problem
142958	6/7/2021 6:23 AM	6/5/2021 6:52 AM	5	5		Ricky Thakur	Fortify	Inquiry
138626	6/7/2021 5:46 AM	5/26/2021 3:00 PM	5	5		Ricky Thakur	Fortify	Problem
121842	2/7/2021 3:12 PM	1/29/2021 5:01 PM	2	2	please call us to validate if we are indeed aware of overdue account first. due to your upgrades and system changes, our auto pay setup was disregarded and we would normally check auto pay accounts.	Sagar More	Portal	Problem

[AllisonKennedy · Accepted solution]

@saud968 , are you looking for the value 4 OR 5? none of the rows in your sample data are 4 && 5: 

Try this instead: 

C-SAT = CALCULATE(COUNT(CSAT), FILTER(CSAT, CSAT[CSAT] = 4 || CSAT[CSAT] 5 ))

[AllisonKennedy · Accepted solution]

@saud968 , are you looking for the value 4 OR 5? none of the rows in your sample data are 4 && 5: 

Try this instead: 

C-SAT = CALCULATE(COUNT(CSAT), FILTER(CSAT, CSAT[CSAT] = 4 || CSAT[CSAT] 5 ))

[saud968]

Thank you that worked I wanted to count both 4 & 5... I saw one video where I saw the guy used && for multiple filter

[AllisonKennedy · Accepted solution]

@saud968  You are very welcome. && will give you an AND condition, so both values must be true. || gives you an OR condition, so the value must be 4 OR 5 in that case. 

[saud968]

@AllisonKennedy I actually wanted both 4 & 5 from CSAT column weirdly || or function gave the perfect count

[AllisonKennedy]

@saud968 I think you've inspired me to do a new blog post on AND vs OR filters, they're not intuitive. 

AND means that for a single row the value must be both 4 AND 5. This is never possible, the value will be 4 OR 5, can't be both in a single row, so you'll get blank.

OR means for the column you want to include ALL values that are equal to either 4 OR 5, so it will look for each single row and say 'does this value equal 4 OR 5?' if yes it will return that row, then it will search the next row and say 'does this value equal 4 OR 5?' if yes, it will return that row. In this manner we get all rows that equal 4 AND we get all rows that equal 5, by checking if EACH row equals 4 OR 5.  Are you confused yet? Hopefully that helps clarify a bit... 

[saud968]

@AllisonKennedy  I am glad I was able to do something good 😅. Yes, it is clear now and understood. I agree that you should be doing the blog for the same. 

[AllisonKennedy]

Blog post is live: https://excelwithallison.blogspot.com/2021/09/advanced-filter-or-vs-and.html in case anyone is interested in learning more about AND OR conditions in advanced filters. I welcome your feedback!