Distinct Count per Group

[newgirl]

Hi! I have a raw data of transactions of our clients, in which some clients belong to the same group which is why there's a column representing their grouping. Ex. Group 1 is an individual client while Group 5 has 4 clients. For my sample raw data, this means that there's a total of 10 groups (I forgot to include number 4).

The main table I need is to count how many groups are there with failed conditions. In this raw data, there are 3 conditions: FB (if client has Facebook), Past Due, and Minimum Points. Below is how a "failed condition" is defined:

• FB - if a group has at least a "No", count as failed
• Past Due - if a group has at least a "Yes", count as failed
• Minimum Points - only if the WHOLE group has "No", then count as failed.

Below is a picture of the raw data, how it's counted, and the table I expect. 

For example with Group 11, we count with 1 failed for FB because the group has at least one "No" and we count with 1 failed for  Minimum Points because the whole group has "No". We count 0 for Past Due because there should be at least one "Yes" to be counted.

Another example with Group 9, we count with 1 failed for Past Due because the group has at least one "Yes". We count 0 for FB because there should be at least one "No" to be counted. We count 0 also for Mininum Points because only 1 of the groupmates had a "No". The WHOLE group should be a "No" to be counted.

Below is my raw data:

Category	Group	Client	Salesperson	FB	Past Due	Minimum Points
Wholesalers	1	11	Homer	Yes	No	Yes
Wholesalers	2	21	Homer	No	Yes	No
Wholesalers	3	31	Homer	Yes	No	Yes
Wholesalers	5	51	Homer	Yes	Yes	Yes
Wholesalers	5	52	Homer	Yes	Yes	Yes
Wholesalers	5	53	Homer	Yes	Yes	Yes
Wholesalers	5	54	Homer	No	Yes	Yes
Wholesalers	6	61	Homer	Yes	Yes	Yes
Wholesalers	6	62	Homer	Yes	No	No
Wholesalers	7	71	Homer	No	Yes	No
Wholesalers	7	72	Homer	No	Yes	No
Wholesalers	7	73	Homer	No	Yes	No
Wholesalers	8	81	Bart	No	Yes	Yes
Wholesalers	8	82	Bart	No	Yes	Yes
Wholesalers	8	83	Bart	No	Yes	Yes
Wholesalers	9	91	Bart	Yes	No	No
Wholesalers	9	92	Bart	Yes	No	Yes
Wholesalers	9	93	Bart	Yes	Yes	Yes
Wholesalers	9	94	Bart	Yes	No	Yes
Wholesalers	10	101	Bart	Yes	No	No
Wholesalers	10	102	Bart	No	No	No
Wholesalers	11	111	Bart	No	No	No
Wholesalers	11	112	Bart	Yes	No	No
Wholesalers	11	113	Bart	Yes	No	No

Hope somebody can help me out with the measure!

[HotChilli · Accepted solution]

OK, I've created a column:

AllNoinGroupMinPoints = var _Group = Sales[Group]
var _rowsInGroup = COUNTROWS(FILTER(Sales, Sales[Group] = _Group))
var _rowsInGroupWithNo = COUNTROWS(FILTER(Sales, Sales[Group] = _Group && Sales[Minimum Points] = "No"))
RETURN
IF(_rowsInGroup = _rowsInGroupWithNo, 1, 0)

and then a measure to use the column:

F_MinPTest = CALCULATE( DISTINCTCOUNT(Sales[Group]), Sales[AllNoinGroupMinPoints] = 1)

Please test at your side.

[newgirl]

Thank you so much @HotChilli ! This worked!

[HotChilli]

I've been trying to post a reply but it's not saving.  This is a test

- edit : it was the code insert that was causing a problem.  I've posted it as text now 

[HotChilli]

I think this will give you a pattern for each measure (sub in table, column names at your side):

MeasureFTest = CALCULATE( DISTINCTCOUNT(TableQ[Group]), TableQ[FB] = "No")

One of the other measures will be similar and one will have comparison to "Yes"

Let me know how you get on

[newgirl]

Hi @HotChilli !

Thanks for your reply! These are the measures I made:

F_FB = CALCULATE( DISTINCTCOUNT(Sales[Group]), Sales[FB] = "No")

F_PastDue = CALCULATE( DISTINCTCOUNT(Sales[Group]), Sales[Past Due] = "Yes")

F_MinP = CALCULATE( DISTINCTCOUNT(Sales[Group]), Sales[Minimum Points] = "No")

Comparing this table with the expected output, only the 3rd measure is showing wrong results. Looks like it counts the group as long as it has a "No". However, I need it to count the group only if the whole group has a "No" .  Any suggestion on how to modify the 3rd measure?

[Greg_Deckler]

@newgirl Seems like you should be able to use a measure along the lines of:

COUNTROWS(DISTINCT(FILTER(ALL('Table'),<filter conditions>)))

or potentially:

COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER(ALL('Table'),<filter conditions>),"column",[Column])))

I wasn't quite following the conditions.