Returning count if value contains specific group

[mcinnisbr]

Hi,

I have a problem in returning proper facility counts based on a strange grouping in my facility type data column.

My data look like this.  

Facility         Type

Facility A	COED
Facility A	MALE
Facility A	FEMALE
Facility B	MALE
Facility C	FEMALE

This is how the data is organized.  Single facilities were arranged with unique ids.  However, a COED facility containing 'male' and 'female' also have a unique  ID and it's all lumped together which makes it difficult to count properly as 1 facility (COED).

Basically, 'Facility A' should be recognized as 'COED'  and not  'FEMALE, and MALE" even though it contains those values.  Someone organized the column like this for a large table.   

If a 'Facility A' contains 3 types  (COED, MALE, FEMALE) I want to recognize the facility as 'COED' and count it as only 1 facility, not all 3 Types.  All other facilities (B and C) with 1 type (either male of female) should be counted as 1 (since there is only 1 type anyway).   I have no clue if a dax measure can help or if i have to break the coloumn apart?  

realistically, if i had a report with cards displaying facility totals,  facility A would return

Facility A

COED 1

MALE - 0

Female- 0

Facility B

COED 0

MALE - 1

Female- 0

Facility C

COED 0

MALE - 0

Female- 1

Any advice would be amazing.  

Thanks.

[Greg_Deckler]

You could create a column: 

Column = 
  VAR __Table = FILTER('Table',[Facility] = EARLIER('Table'[Facility])
RETURN
  IF(COUNTROWS(__Table = 1,[Type],"COED")

[bheepatel · Accepted solution]

Hi @mcinnisbr 

Can you try the following:

Assume that Table1 is the table you created with the columns Facility and Type.

1. Create a new table (Table2) with the following DAX code:

Column1 = Distinct('Table1'[Facility])

2. In this new table, create a second column with the following DAX code:

Column2 = LOOKUPVALUE('Table1'[Type], 'Table1'[Facility], 'Table2'[Column1], "COED")

You should have a table of distinct facilities and a single type.

Hope this helps!

[mcinnisbr]

Thank you!   I'm trying to understand how this works, but it does for sure.  Would the related function work with this?  

[Greg_Deckler]

You should be able to use RELATED or RELATEDTABLE with this technique. 

Basically, for each row it is filtering the entire table for all rows in the table that have the current value of the column specified by EARLIER (think current row's value for that column). Then you just count the number of rows returned. For the COED ones, there will be 3 but for the others there will only be 1.