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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
dandamudisanjay
Regular Visitor

Help with Data modelling

Hi All, 

 

We have our data as follows. 

IDCricketFootballBaseballHockeyRugbyMotorsportBasketballTennisGolf

1

TRUETRUETRUEFALSEFALSEFALSEFALSEFALSETRUE

2

FALSEFALSEFALSETRUEFALSEFALSEFALSEFALSEFALSE
3FALSEFALSEFALSEFALSEFALSEFALSEFALSETRUEFALSE
4FALSETRUEFALSETRUEFALSETRUETRUEFALSEFALSE
5FALSEFALSEFALSEFALSEFALSEFALSEFALSETRUEFALSE
6TRUEFALSEFALSETRUEFALSEFALSEFALSEFALSETRUE
7FALSEFALSEFALSEFALSEFALSEFALSETRUETRUEFALSE

 

We are looking to create a visualisation which shows total ID's along with Sport Name and interest as TRUE only. so the output we are looking to get is something like follows but in a visualisation with common axes as total of ID's

SportTotal
Cricket2
Football2
Baseball1
Hockey3
Rugby0
MotorSport1
Basketball2
Tennis3
Golf2

 

Could anyone please help with this?

 

Will highly appreciate your help with this. 

 

Regards

Sanjay

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @dandamudisanjay ,

 

Here what you want to achieve is to unpivot your dataset, to go from what you have to:

Customer ID / Sport

1 Cricket
1 Football
1 Baseball
1 Golf
2 Hockey
3 Tennis
....

 

You'll find here a .pbix with an example based on your use case.

 

The steps I took are the following

1. Unpivot and prepare your data in Power Query

2. Filter out rows for which the value is false

3. Create a measure that counts the number of players per sport

 

 

Step 1 : unpivoting the data

unpivot.PNG

- you select the columns with names of sport

- and then Transform > Unpivot Columns > Unpivot Columns

 

The next steps in Power Query are:

- renaming Attribute to something more meaningful like Sport

- filtering out on the value column rows with FALSE

- removing the value column which is now useless as it only contains TRUE

 

Step 2 : creating your measure in Power BI

Here it's simple:

 

Count of Players = COUNTROWS( SportsData )

 

Step 3 : create your visual

sports_count_result_v2.PNG

Also, there are some great content which explain you more about the unpivot step in PowerQuery, I'll let you search the web/youtube.

 

I hope it helps. Does it?

 

Best,

Thomas

 

 

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

Hi @dandamudisanjay ,

 

Here what you want to achieve is to unpivot your dataset, to go from what you have to:

Customer ID / Sport

1 Cricket
1 Football
1 Baseball
1 Golf
2 Hockey
3 Tennis
....

 

You'll find here a .pbix with an example based on your use case.

 

The steps I took are the following

1. Unpivot and prepare your data in Power Query

2. Filter out rows for which the value is false

3. Create a measure that counts the number of players per sport

 

 

Step 1 : unpivoting the data

unpivot.PNG

- you select the columns with names of sport

- and then Transform > Unpivot Columns > Unpivot Columns

 

The next steps in Power Query are:

- renaming Attribute to something more meaningful like Sport

- filtering out on the value column rows with FALSE

- removing the value column which is now useless as it only contains TRUE

 

Step 2 : creating your measure in Power BI

Here it's simple:

 

Count of Players = COUNTROWS( SportsData )

 

Step 3 : create your visual

sports_count_result_v2.PNG

Also, there are some great content which explain you more about the unpivot step in PowerQuery, I'll let you search the web/youtube.

 

I hope it helps. Does it?

 

Best,

Thomas

 

 

Thanks a lot Thomas. Worked like a treat.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors