Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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!
You could create a column:
Column =
VAR __Table = FILTER('Table',[Facility] = EARLIER('Table'[Facility])
RETURN
IF(COUNTROWS(__Table = 1,[Type],"COED")
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!
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
45 | |
31 | |
30 | |
18 | |
17 |