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 everyone,
I have some survey results where each user had the opportunity to select multiple answers to analyze and I am a bit stuck...
Here is a snapshot of the data:
As you guys can see from the above, a player can play multiple games on multiple devices.
So what I wanted to display on my dashboard is a table or bar chart of the consoles along with a slicer for the games played.
To achieve that, I did the following:
Step 1: Unpivot the Games. Here is the result:
Step 2: Unpivot the above but for the consoles; here is the result:
Step 3: On the canvas, I did the following:
And this works well; the numbers tally up properly!
However, if I were to remove all the filters (No selection), the numbers do not make sense anymore.
Here how it looks like:
I would like the table to just show the number of times the game was played irrespective of the console when there is no selection.
That is:
Fortnite: 18
FIFA 19: 11
Need For Speed:10
Call of Duty: 6
NBA 2K19: 3
Can anyone please help me with this? Please me know if there is a better way of doing it.
Thank you very much
Solved! Go to Solution.
Great. So what you actually are trying to do, is to show the number of respondents pr game and device. There is nothing wrong with your code, but your dataset has some issues. There is no way of identifying a respondent. You can change this by adding a column in your excel file with a uniqie number pr row, or you can add an index column in power query.
I would have shared a file with you showing how it could be done with power query, but it seems like you are using an older version of power bi desktop, so you would not be able to open any file I send you.
cheers,
S
Hi,
I think what you have done should work, and I am not able to recreate the behaviour you experience. Perhaps you could upload a sample of your file to dropbox/onedrive/other and share the link here?
cheers,
S
Hi,
Thank you for your reply.
Sure, here is the link:
PowerBI File: https://drive.google.com/file/d/1fNGBNa0rUM7dAbZypnBhsQ_vMsUq8k21/view?usp=sharing
Excel Sheet: https://drive.google.com/file/d/146aeMHxVeyA0k87Kf56tM5DI2iPKocu7/view?usp=sharing
Thanks
Great. So what you actually are trying to do, is to show the number of respondents pr game and device. There is nothing wrong with your code, but your dataset has some issues. There is no way of identifying a respondent. You can change this by adding a column in your excel file with a uniqie number pr row, or you can add an index column in power query.
I would have shared a file with you showing how it could be done with power query, but it seems like you are using an older version of power bi desktop, so you would not be able to open any file I send you.
cheers,
S
Ohh ok!
So I added the index column to the table.
then my value count is the "Distinct Count" of the index... So we can identify individual respondents.
That seems to work!
Thank you
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 |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |