Show and count matching data from 1 table

[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 customers that reached by all 3 companies. 

Result 1: 

CompanyName	IsNotCovering
A	BBB
A	EEE
B	BBB
B	DDD
C	AAA
C	EEE

Result 2:  Company "B" covers but C does not:

CompanyName	IsNotCovering
C	AAA
C	EEE

Result 3:  all companies with the customers that they are not covering.

CompanyName	IsNotCovering
A	BBB
A	EEE
B	BBB
B	DDD
C	AAA
C	EEE

I would like to do it using DAX measure, new column.  without using Filter Pane.  (or maybe also not using Slicer (If possible.))

I did search from google and Power BI forums. but did not quite found what I wanted. 

[amitchandak · Accepted solution]

@Anonymous , Try like

not sold to customer , this will give a table , can be used in a measure -all companies with the customers that they are not covering.
new Table = except(crossjoin(distinct(Table[CustomerName]),distinct(Table[CompanyName])), Table)

The customers reached by all 3 companies.
Measure = countx(filter(summarize(Table, Table[CustomerName] ,"_1", distinctCOUNT(Table[CompanyName]) ,"_2", calculate(distinctCOUNT(Table[CompanyName]), all(Table))),[_1]=[_2]),[CustomerName])

Result 2: Company "B" covers but C does not:
new Table 2 = except(summarize(filter(Table,Table[CompanyName] ="B"),Table[CustomerName]),summarize(filter(Table,Table[CompanyName] ="C"),Table[CustomerName]))

measure = calculate(count(Table[CustomerName]), filter(Table,Table[CustomerName] in except(summarize(filter(Table,Table[CompanyName] ="B"),Table[CustomerName]),summarize(filter(Table,Table[CompanyName] ="C"),Table[CustomerName])) && Table[CompanyName] ="B")

[Anonymous]

Wow. thank you very much, sir. Everything is working like magic. 

/* 

Could you please tell me what does the last measure do? the result is 2. but I am not quite sure that that is counting. 

this one ->   measure = calculate(count(Table[CustomerName]), filter(Table,Table[CustomerName] in except(summarize(filter(Table,Table[CompanyName] ="B"),Table[CustomerName]),summarize(filter(Table,Table[CompanyName] ="C"),Table[CustomerName])) && Table[CompanyName] ="B")

*/ -- I got this one. If I am not wrong, this one is the measure to count: "Result 2: Company "B" covers but C does not:" 

edit: 

not sold to customer , this will give a table , can be used in a measure -all companies with the customers that they are not covering.

NewTable = EXCEPT(CROSSJOIN(DISTINCT('Table'[CustomerName]),DISTINCT('Table'[CompanyName])), 'Table')

How Can I add a filter to this? for example,

1. Show a list of all companies (except company C) with the customers that they are not covering. 

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