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.
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?
Hi @bryanc78 ,
Can't you just use your refresnced table for all the visualisations?
In that way you don't need the other table.
Basically do everything on a single table and use it for visualisations.
Thanks,
Pragati
Since I'm not unpivotting all the columns, wouldn't that cause my other fields to basically be duplicated 30 times?
Hi @bryanc78 ,
This should not create any issue. It will rather help with loading less tables in Power BI ans improve performance.
Thanks,
Pragati
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
Hi,
So now in your visual, if you wish to know the score per name, then drag Namr to the visual and write this measure
=min(Data[Score])
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
Hi,
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
Hi,
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
Hi,
Do you have a Test ID column in your base data?
@Ashish_Mathur yes, I use that field to connect the referenced table to the main table
Hi,
If you have a TestID, then after unpivoting, you may use this measure
=distinctcount(Data[TestID])
Hope this helps.
Hi,
Yes, that will. But that should not cause problems.
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 |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |