Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Anonymous
Not applicable

Array? A new table? Creating one race/ethnicity for students who reported more than one

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 IDNameY/TIPEDS_codeEthnicity/Race
14Ashlee2023/SPRING4Black or African American
14Ashlee2023/SPRING6White
35Barbara2023/FALL6White
35Barbara2023/SPRING6White
57Cassy2023/SPRING4Black or African American
57Cassy2023/SPRING6White
61Dinea2023/FALL6White
61Dinea2023/SPRING6White
63Effie2023/SPRING4Black or African American
63Effie2023/FALL4Black or African American
70Fanny2023/FALL72 or more
72Ginny2023/SPRING6White
82Heaven2023/SPRING1Hispanic
82Heaven2023/FALL1Hispanic
168Itzel2023/SPRING3Asian
258Jamila2023/SPRING1Hispanic
258Jamila2023/SPRING6White
258Jamila2023/FALL1Hispanic
258Jamila2023/FALL6White
258Karmen2022/SPRING1Hispanic
258Karmen2022/SPRING6White
259Lacey2023/SPRING2American Indian or Alaska Native
259Lacey2023/SPRING4Black or African American
280Miriam2023/SPRINGNULLNULL
1 ACCEPTED SOLUTION
parry2k
Super User
Super User

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

View solution in original post

4 REPLIES 4
parry2k
Super User
Super User

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

Ashish_Mathur
Super User
Super User

Hi,

Based on the table that you have shared, show the expected result very clearly.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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 IDNameY/TIPEDS_codeEthnicity/RaceFinal Ethnicity/Race
14Ashlee2023/SPRING4Black or African American2 or more
14Ashlee2023/SPRING6White2 or more
35Barbara2023/FALL6WhiteWhite
35Barbara2023/SPRING6WhiteWhite
57Cassy2023/SPRING4Black or African American2 or more
57Cassy2023/SPRING6White2 or more
61Dinea2023/FALL6WhiteWhite
61Dinea2023/SPRING6WhiteWhite
63Effie2023/SPRING4Black or African AmericanBlack or African American
63Effie2023/FALL4Black or African AmericanBlack or African American
70Fanny2023/FALL72 or more2 or more
72Ginny2023/SPRING6WhiteWhite
82Heaven2023/SPRING1HispanicHispanic
82Heaven2023/FALL1HispanicHispanic
168Itzel2023/SPRING3AsianAsian
258Jamila2023/SPRING1HispanicHispanic
258Jamila2023/SPRING6WhiteHispanic
258Jamila2023/FALL1HispanicHispanic
258Jamila2023/FALL6WhiteHispanic
258Karmen2022/SPRING1HispanicHispanic
258Karmen2022/SPRING6WhiteHispanic
259Lacey2023/SPRING2American Indian or Alaska Native2 or more
259Lacey2023/SPRING4Black or African American2 or more
280Miriam2023/SPRINGNULLNULLNot reported

Another contributor has answered your question.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.