I have a datset with about 50 columns that I'm bringing into PBI
Table 1 - only uses about 5 of them
Table 2 - is a duplicate (reference) of Table 1 where I take about 30 of those columns and then unpivot them. Again, on this table, I only use about 5 columns.
I know I can bring in the dataset twice and delete/unpivot as needed but that's 2 datasets I have to manage and refresh.
Any other options?
If that's the case, then no need to create the 2nd reference table. I'll just unpivot the initial table. With the unpivotted columns being duplicated 30 times, I just assumed it would make any aggregating useless
I'll try it shortly and report back here my findings
This did not work. I should have only 500 items in my bar chart but after unpivoting the main table, I now have over 20k items
Is there a setting that I'm missing?
My dataset is basically a test with 30 questions going across 50 columns
name date score question 1 question 2 question 3 etc
I'm unpittoing all the questions to make it easier to chart but now "score" is being duplicated 30 times
So now in your visual, if you wish to know the score per name, then drag Namr to the visual and write this measure
Hope this helps.
That did not help. I want to report on the Score and not the name
So, if I had 90 Pass and 10 Fail, I want the bar chart to show 90 and 10
Right now, one test is being counted 30 times
Share data in a format that can be pasted in MS Excel and also show your expected result.
Here is the data that is in my csv file that PBI is connected to
Name Date Score Question 1 Question 2 Question 3 Question 4 Question 5 Question 6 Bob 7/1/2020 Pass Red Orange Football TV Bourbon Book
Here is how it looks when I unpivot all the "question" columns
Name Date Score Questions Question Answers Bob 7/1/2020 Pass Question 1 Red Bob 7/1/2020 Pass Question 2 Orange Bob 7/1/2020 Pass Question 3 Football Bob 7/1/2020 Pass Question 4 TV Bob 7/1/2020 Pass Question 5 Bourbon Bob 7/1/2020 Pass Question 6 Book
If just unpivot the original table, every column that was not unpivoted gets duplicated 30 times so I cant get accurate counts on it. What I want to happen is to only have the unpivoted columns count once and I'm not sure if that is possible or not
What do you want to calculated. Please be very clear. Show the expected result clearly.
I want to be able to count the total number of tests that passed, the total number of tests that failed, the total number of tests that Bob took etc
If Bob took 1 test that contained 30 questions and he passed it, I want it to show that
I do not want it to show that:
Please see below. The chart on the left is correct. The chart on the right is pointing to the same data fields but on the unpivoted columns. As you can see, each test is being duplicated 30+ times
Do you have a Test ID column in your base data?
If you have a TestID, then after unpivoting, you may use this measure
Hope this helps.
Yes, that will. But that should not cause problems.
Learn how to create your own user groups today!
Click here to read more about the November 2021 Updates!
Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.