Filter Name Column with Number Column

[Anonymous]

Hi people,

I have the following table:

I want to show in a diagram how often the teams are working with the same Ordernumber.

There are 4 Teams: AAA, BBB, CCC, DDD

Diagram 1 should show how many OrderNumbers where used by every one of the four teams independently (Just be counted when no other team used the Number. And just be counted once, even if the number appears multiple times for that Team)

Diagram 2 should show how many OrderNumbers where used by Team Combinations, e.g. by AAA and BBB together. Or by BBB, CCC and DDD together. The Number should also be counted just once, even if it appears multiple times per team.

Thanks in advance!

[Greg_Deckler]

@Anonymous - Seems like you could just put Ordernumber (no aggregation) in a table along with a count of Team. Then you could just filter on the count of Team.

If that is not sufficient, please post data as text.

[Anonymous]

Hi @Greg_Deckler ,

thanks for your approach! The problem that appears, when I'm doing it like this is, that it doesn't subtract numbers that appear multiple times in one team (should just be counted as "1", even if it appears 5 times for a team.

It also doesn't subtract the Ordernumbers that are used by multiple teams.

This is the data as a text

Team	Ordernumber
AAA	1111
AAA	1111
BBB	1111
BBB	2222
AAA	2222
CCC	2222
DDD	2222
CCC	3333
DDD	4444
BBB	5555
CCC	5555
AAA	6666
AAA	7777
DDD	7777
AAA	8888
BBB	9999

Its just an example I made.

Diagram 1 would show:

AAA = 2

BBB =  1

CCC =  1

DDD = 1

Diagram 2 would show:

AAA & BBB = 1

BBB & CCC = 1

AAA & DDD = 1

AAA & CCC = 0

BBB & DDD = 0

CCC & DDD = 0

AAA & BBB & CCC = 0

AAA & BBB & DDD = 0

AAA& CCC & DDD = 0

BBB & CCC & DDD = 0

AAA & BBB & CCC & DDD = 1

[Greg_Deckler]

@Anonymous - In that case, create a measure and do a SUMMARIZE or GROUPBY by team, then just count the rows in that summarized/grouped table. That will eliminate counting teams multiple times.

Or, use DISTINCT to get the distinct teams and count the rows (COUNTROWS) returned. Same thing as SUMMARIZE or GROUPBY but perhaps cleaner.

[Anonymous]

Thank you @Greg_Deckler 

I think I made the right measure:

Measure = COUNTROWS(FILTER(DISTINCT(Ordernumber),CALCULATE(COUNTROWS(Table)) = 1))

But it doesn't show me the right numbers yet. I think it is because I didn't group the Teams for Diagram 2 yet. What would be the best way to do it? It seems like there is no right tool inside Power Bi to group them. Should I create a new table? I'm not sure how the rows should look like.