Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
heidibb
Helper IV
Helper IV

Counting Values to use on rows of matrix

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??Capture.JPG

1 ACCEPTED 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.Capture.JPG

View solution in original post

3 REPLIES 3
BobBI
Resolver III
Resolver III

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])

 

table matrix.png

 

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

sample.png

 

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.Capture.JPG

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.

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.