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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
iloobi
New Member

Merging fields to count as one

Hello,

 

I am very new to PowerBI and potentially this is an easy question so my apologies if it seems stupid to ask this. I have data in this format

 

Project namePopulation served (1st choice)Population served (2nd choice)Population served (3rd choice)
DancingWomen and girlsChildren and Youth 
SingingChildren and youthLow income individualsIndigenous
ReadingIndigenous  
WritingRacialized communitiesChildren and Youth 

 

Right now, when try to build a visualization in desktop, I am only able to count these categories seperately. Like in this decomposition tree, I can only follow the thread of which projects are serving children and youth as first choice, then people living with mental health issues as a second choice, etc. 

iloobi_0-1667589279682.png

Or on this dashboard, I need individual splicers for each choice to be able to filter for each choice

 

iloobi_1-1667589422371.png

What I would like is to be able to somehow total up each category. So that regardless of which choice it is mentioned at, it would count towards a total. So like in the sample table, Children and Youth are served in 3 different projects in 3 different choices. I want the decomposition table to be able to total that up instead of spliting them by the choice selection. I imagine I will have to create a new field/column somehow in transforming the data but I have no idea how to do that. Could something be comma seperated or semicolon seperated but PowerBI would be able to recognize the individual values?

 

I tried create true/false columns for each population type but I have over 12 population types listed and couldn't find an easy way to filter on the dashboard without creating 12 different splicers. 

 

I hope my question makes sense and that someone knows of a work around that would help. 

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@iloobi In Power Query Editor, select those 3 columns and then unpivot.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
iloobi
New Member

@Greg_Deckler Thank you

Greg_Deckler
Super User
Super User

@iloobi In Power Query Editor, select those 3 columns and then unpivot.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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