cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Returning count if value contains specific group

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).

 

images-ask-pbi.JPG

 

 

 

 

 

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.

 

 

1 ACCEPTED SOLUTION
Resolver III
Resolver III

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!

View solution in original post

4 REPLIES 4
Super User IV
Super User IV

You could create a column: 

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

 

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Resolver III
Resolver III

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!

View solution in original post

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

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.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.