Forum Discussion
Show and count matching data from 1 table
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.
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")
2 Replies
- amitchandak
Super User
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")
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"