Please help me add some filters to some functions

[Anonymous]

Hello, 

there are 3 companies and a lot of customers. I would like to see Customers that are not reached by Each company and count numbers etc. . .  this is the table I have. 

CustomerName	CompanyName
AAA	A
AAA	B
BBB	C
CCC	A
CCC	B
CCC	C
DDD	A
DDD	C
EEE	B

the result I want to see is: The name of the customer list that reached by all 3 companies. and also by 2 companies or 1. without using  Filter Pane.  (or maybe also not using Slicer (If possible.))

These are the queries that @amitchandak  (thanks to @amitchandak ) has written it for me. they are working great. but I would like to add some filters to it. but I am having a hard time figuring out how. 

This is the original link of how these functions came out for those who are interested: Click here

1. not sold to customers, this will give a table, can be used in a measure -all companies with the customers that they are not covering. How can I add a filter to this? for example, I would like to exclude the company "2" from here. to show only not covered customers by company"1" and company"2" 
new Table = except(crossjoin(distinct(Table[CustomerName]),distinct(Table[CompanyName])), Table)

2. The number of customers reached by all 3 companies. (How can I add an additional filter to it? for example excluding company"2")
Measure = countx(filter(summarize(Table, Table[CustomerName] ,"_1", distinctCOUNT(Table[CompanyName]) ,"_2", calculate(distinctCOUNT(Table[CompanyName]), all(Table))),[_1]=[_2]),[CustomerName])

3. I would like to show a list of all CustomerNames visited by all Companies except Company"C"

[amitchandak]

@Anonymous , first of all as all is not used in most of the , they should respond to filter

First one you can add calculatetable

new Table = except(crossjoin(distinct(calculatetable(Table[CustomerName], filter(Table,Table[] =<Condition>)) ),distinct(calculatetable(Table[CustomerName], filter(Table,Table[] =<Condition>))),calculatetable(Table, filter(Table,<condition>))

in the second in summarize you can use filter

summarize(filter(Table, <condition>

for  3rd one one you can have filter Table[CompanyName] <>"C"

Also, refer

https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

[Anonymous]

Thank you very much, Sir @amitchandak ! 

I got 2nd and 3rd one. I also will read that link as soon as possible. 

Could you please help me with the first function. I tried to move brackets quite a lot. but still having error. 

  

this is my pbix file 

Again. thank you very much, sir

[amitchandak]

@Anonymous , cross join need renaming. No same column name, Try select column

Table3 = except(crossjoin(
    distinct(
        SELECTCOLUMNS(filter('Table','Table'[CompanyName] <>"C"),"CompanyName",'Table'[CompanyName])),
    distinct(
        SELECTCOLUMNS( filter('Table','Table'[CompanyName] <>"C"),"CustomerName",'Table'[CustomerName]))),
        calculatetable('Table', filter('Table','Table'[CompanyName]<>"C")))

[Anonymous]

@amitchandak  . 

It is still giving me an error.
Could you please take a look at my pbix file? the table I am working on is called: "table3". 

this is the file link!  

[V-pazhen-msft · Accepted solution]

@Anonymous 
I don't see need of using except() function or if I misunderstood your requirement, try:

NewTable3 = CALCULATETABLE(DISTINCT('Table'[CustomerName]),FILTER('Table','Table'[CompanyName]<>"A" && 'Table'[CompanyName]<>"B"))

If not work, Please show your expected output table of the sample. 

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

this is working perfectly. this is exactly what I was looking for. thanks a lot! 

Have a good day, Sir!