Forum Discussion

bswud's avatar
bswud
New Member
7 years ago
Solved

Counting character values from multiple columns

I've scrolled at least 20 help threads to no avail—amazes me if there isn't a simple solution out there—but no one has been looking for precisely the same thing as me.   So I have a dataset where s...
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi bswud

    Sorry not to explaining better.

    Before creating measure, i go to "edit queries"->"Transform", select four subject columns, then select "unpivot columns", then "apply&&colse", next create measures.

    Below is the table after transform

     

     

    Best Regards

    Maggie

     

     

v-juanli-msft's avatar
v-juanli-msft
Community Support
7 years ago

Hi bswud

Assume your table is like

"1" represents the student has entries in this subject.

Then in Edit quieries, select all subject columns from "math" to "music", then in "Transform"->"Unpivot columns", the dataset would change as below

Then in Report view, create a measure to count the number of entries in each subject for all students

Measure = CALCULATE(COUNT(Sheet4[Value]),ALLEXCEPT(Sheet4,Sheet4[Attribute]))

But, for your final requirement about total time, i can't figure out since i don't know how your data look like.

Please share some data example with me so that i can help you efficiently.

 

Best Regards

Maggie

  • bswud's avatar
    bswud
    New Member
    7 years ago

    Thank you for the reply. No that is not at all how it looks, my apologies for describing it poorly. I can't share screenshots of the data itself because it's from a private project, but I'll mock up some similar data for explanation.

     

     

     

    It looks like this. I want to count all the instances of all the unique subjects across all four columns. It's trivially easy to count them across one column, so I can see the "main" subject easily. But I can't get a summed chart across all four.