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 All,
We have our data as follows.
ID | Cricket | Football | Baseball | Hockey | Rugby | Motorsport | Basketball | Tennis | Golf |
1 | TRUE | TRUE | TRUE | FALSE | FALSE | FALSE | FALSE | FALSE | TRUE |
2 | FALSE | FALSE | FALSE | TRUE | FALSE | FALSE | FALSE | FALSE | FALSE |
3 | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | TRUE | FALSE |
4 | FALSE | TRUE | FALSE | TRUE | FALSE | TRUE | TRUE | FALSE | FALSE |
5 | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | TRUE | FALSE |
6 | TRUE | FALSE | FALSE | TRUE | FALSE | FALSE | FALSE | FALSE | TRUE |
7 | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | TRUE | TRUE | FALSE |
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
Sport | Total |
Cricket | 2 |
Football | 2 |
Baseball | 1 |
Hockey | 3 |
Rugby | 0 |
MotorSport | 1 |
Basketball | 2 |
Tennis | 3 |
Golf | 2 |
Could anyone please help with this?
Will highly appreciate your help with this.
Regards
Sanjay
Solved! Go to Solution.
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
- 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
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
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
- 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
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.
@dandamudisanjay , If better to unpivot this data
https://radacad.com/pivot-and-unpivot-with-power-bi,
Refer: https://www.youtube.com/watch?v=2HjkBtxSM0g
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/
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 |
---|---|
104 | |
96 | |
79 | |
67 | |
62 |
User | Count |
---|---|
137 | |
106 | |
104 | |
81 | |
63 |