Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I am new to Power BI Desktop, but I have been using Tableau for years. I have students who have choosen more than one race/ethnicity, which causes duplicates in my data set. In Tableau, I use a "level of detail" to deal with it. I think in Excel, I would use an array.
The duplicates mean that when counting students by semester, I have to use Distinct Count. Sometimes that gets strange. For example, when I am summing the students credits, Ashlee's credits get summed twice, because she has two lines for the one semester. I think if I collapse the duplicates (such as changing Ashlee to "2 or more") in Power Query, then I can remove the two columns that are causing the duplication.
My data is one line per student per semester. If they have more than one race/ethnicity, then there are two lines each semester. In the example below, Ashlee is both White and Black. Jamila is Hispanic and White. Lacey is Indian and Black. Others are just one race/ethnicity, and Miriam didn't report her race/ethnicity.
IPEDS (Department of Education) gives us the codes, which is helpful. If a person is Hispanic, that overrides all other races. Jamila is "Hispanic." If a person has a null (Miriam), then she is "Not Reported." Ashlee and Lacey need to show as "2 or more," and all the rest should be whatever their unique race is.
Please do your magic on my data!
This is how the Level of Detail is done in Tableau:
IF {FIXED[Student ID]:MAX(IFNULL(INT([IPEDS code]),0))}=0 THEN "Not reported"
ELSEIF {FIXED[Student ID]:MIN(IFNULL(INT([IPEDS code]),0))}=1 THEN "Hispanic"
ELSEIF {FIXED[Student ID]:MAX(IFNULL(INT([IPEDS code]),0))}={FIXED[Student ID]:MIN(IFNULL(INT([IPEDS code]),0))} THEN [Ethnicity/Race (dups)]
ELSEIF {FIXED[Student ID]:MAX(IFNULL(INT([IPEDS code]),0))}!={FIXED[Student ID]:MIN(IFNULL(INT([IPEDS code]),0))} THEN "2 or more"
ELSE "Error" END
This is a snippet of my data set:
Student ID | Name | Y/T | IPEDS_code | Ethnicity/Race |
14 | Ashlee | 2023/SPRING | 4 | Black or African American |
14 | Ashlee | 2023/SPRING | 6 | White |
35 | Barbara | 2023/FALL | 6 | White |
35 | Barbara | 2023/SPRING | 6 | White |
57 | Cassy | 2023/SPRING | 4 | Black or African American |
57 | Cassy | 2023/SPRING | 6 | White |
61 | Dinea | 2023/FALL | 6 | White |
61 | Dinea | 2023/SPRING | 6 | White |
63 | Effie | 2023/SPRING | 4 | Black or African American |
63 | Effie | 2023/FALL | 4 | Black or African American |
70 | Fanny | 2023/FALL | 7 | 2 or more |
72 | Ginny | 2023/SPRING | 6 | White |
82 | Heaven | 2023/SPRING | 1 | Hispanic |
82 | Heaven | 2023/FALL | 1 | Hispanic |
168 | Itzel | 2023/SPRING | 3 | Asian |
258 | Jamila | 2023/SPRING | 1 | Hispanic |
258 | Jamila | 2023/SPRING | 6 | White |
258 | Jamila | 2023/FALL | 1 | Hispanic |
258 | Jamila | 2023/FALL | 6 | White |
258 | Karmen | 2022/SPRING | 1 | Hispanic |
258 | Karmen | 2022/SPRING | 6 | White |
259 | Lacey | 2023/SPRING | 2 | American Indian or Alaska Native |
259 | Lacey | 2023/SPRING | 4 | Black or African American |
280 | Miriam | 2023/SPRING | NULL | NULL |
Solved! Go to Solution.
@Anonymous add a new column with following DAX expression, you can tweak it as you see fit:
Final Ethnicity/Race =
VAR __StudentId = Race[Student ID]
VAR __StudentTable =FILTER ( ALL ( Race ), Race[Student ID] = __StudentId )
VAR __isHispanic = NOT ISEMPTY ( FILTER ( __StudentTable, Race[Ethnicity/Race] = "Hispanic" ) )
VAR __isNull = NOT ISEMPTY ( FILTER ( __StudentTable, Race[Ethnicity/Race] = "Null" ) )
VAR __CountDistinct = COUNTROWS ( SUMMARIZE ( __StudentTable, [Ethnicity/Race] ) )
RETURN
SWITCH (
TRUE (),
__isHispanic, "Hispanic",
__isNull, "Not Reported",
__CountDistinct > 1, "2 or more",
Race[Ethnicity/Race]
)
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@Anonymous add a new column with following DAX expression, you can tweak it as you see fit:
Final Ethnicity/Race =
VAR __StudentId = Race[Student ID]
VAR __StudentTable =FILTER ( ALL ( Race ), Race[Student ID] = __StudentId )
VAR __isHispanic = NOT ISEMPTY ( FILTER ( __StudentTable, Race[Ethnicity/Race] = "Hispanic" ) )
VAR __isNull = NOT ISEMPTY ( FILTER ( __StudentTable, Race[Ethnicity/Race] = "Null" ) )
VAR __CountDistinct = COUNTROWS ( SUMMARIZE ( __StudentTable, [Ethnicity/Race] ) )
RETURN
SWITCH (
TRUE (),
__isHispanic, "Hispanic",
__isNull, "Not Reported",
__CountDistinct > 1, "2 or more",
Race[Ethnicity/Race]
)
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi,
Based on the table that you have shared, show the expected result very clearly.
Good morning Ashish!
The "Final Ethnicity/Race" column is what I am looking for. Then I could delete the IPEDS_code and the Ethnicity/Race columns and that would remove the duplicates.
Student ID | Name | Y/T | IPEDS_code | Ethnicity/Race | Final Ethnicity/Race |
14 | Ashlee | 2023/SPRING | 4 | Black or African American | 2 or more |
14 | Ashlee | 2023/SPRING | 6 | White | 2 or more |
35 | Barbara | 2023/FALL | 6 | White | White |
35 | Barbara | 2023/SPRING | 6 | White | White |
57 | Cassy | 2023/SPRING | 4 | Black or African American | 2 or more |
57 | Cassy | 2023/SPRING | 6 | White | 2 or more |
61 | Dinea | 2023/FALL | 6 | White | White |
61 | Dinea | 2023/SPRING | 6 | White | White |
63 | Effie | 2023/SPRING | 4 | Black or African American | Black or African American |
63 | Effie | 2023/FALL | 4 | Black or African American | Black or African American |
70 | Fanny | 2023/FALL | 7 | 2 or more | 2 or more |
72 | Ginny | 2023/SPRING | 6 | White | White |
82 | Heaven | 2023/SPRING | 1 | Hispanic | Hispanic |
82 | Heaven | 2023/FALL | 1 | Hispanic | Hispanic |
168 | Itzel | 2023/SPRING | 3 | Asian | Asian |
258 | Jamila | 2023/SPRING | 1 | Hispanic | Hispanic |
258 | Jamila | 2023/SPRING | 6 | White | Hispanic |
258 | Jamila | 2023/FALL | 1 | Hispanic | Hispanic |
258 | Jamila | 2023/FALL | 6 | White | Hispanic |
258 | Karmen | 2022/SPRING | 1 | Hispanic | Hispanic |
258 | Karmen | 2022/SPRING | 6 | White | Hispanic |
259 | Lacey | 2023/SPRING | 2 | American Indian or Alaska Native | 2 or more |
259 | Lacey | 2023/SPRING | 4 | Black or African American | 2 or more |
280 | Miriam | 2023/SPRING | NULL | NULL | Not reported |
Another contributor has answered your question.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
103 | |
98 | |
90 | |
70 |
User | Count |
---|---|
166 | |
131 | |
129 | |
102 | |
98 |