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.
Hello Community!
I'm hoping you can help with this. I have a matrix created (picture attached). What I would like to do is count the number of cells that are populated for each person (row) and add that count to the rows.
So my rows would be Name, Start Date, # Courses Taken
If we're looking at the first row of my picture, that value would be 10. The second row would be 4. I would also like to be able to sort on that field as well so we're showing the people that have taken the most courses at the top.
Possible??
Solved! Go to Solution.
Thank you so much!
I used your tip to duplicate the data set, but I did it slightly differently. After a few steps, my matrix data set ended up being the person's ID and the sum of the courses taken so I had one unique row per person. Then I could join that "matrix" data set to my main data set and pull that sum of courses taken into my view so I could group and sort on those that have takein the most/least courses. I ultimately needed that # placed into my existing matrix.
Hi,
Create a similar data set for easy reference. table name = Matrix
Step1 : select all columns ( 1-14 ) where course name mentions --> unpivot them in query editor. ( see the screenshot after unpivot of data)
step2 : added a coulumn called 'Course' = if(Len(Value)>0,1," ") - you can poluate 1 or 0 if you like ( idea is to calculate sum of this column)
Step3: create a measure 'Number of Course' = sum(Matrix[course])
Drag Name and Number of couse in the table to verify value and play around to create chart of any report.here below is result
Hope this helps.
Bob.
Thank you so much!
I used your tip to duplicate the data set, but I did it slightly differently. After a few steps, my matrix data set ended up being the person's ID and the sum of the courses taken so I had one unique row per person. Then I could join that "matrix" data set to my main data set and pull that sum of courses taken into my view so I could group and sort on those that have takein the most/least courses. I ultimately needed that # placed into my existing matrix.
Great , i wouldn't expect this to exactly same as i did, but more to give an idea to achieve your desire result 🙂
are you able to delivery your report . please post your question if you still have any.
Have a good day.
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 |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |