Forum Discussion

bmarshall92's avatar
bmarshall92
New Member
4 years ago
Solved

Grouped Count Across Multiple Columns

I'm working with a dataset of students where I need a total enrollment count based on majors. The problem is that majors are spread across multiple columns like below (fake data).   Term ID Ma...
Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi bmarshall92 ,

    According to your description, here's my solution.

    Create a new table.

    Table 2 = 
VAR _Major1=SUMMARIZE('Table','Table'[Major1],"Count",COUNT('Table'[Major1]))
VAR _Major2=SUMMARIZE('Table','Table'[Major2],"Count",COUNT('Table'[Major2]))
VAR _Major3=SUMMARIZE('Table','Table'[Major3],"Count",COUNT('Table'[Major3]))
VAR _Major=UNION(_Major1,_Major2,_Major3)
RETURN
FILTER(_Major,[Major1]<>BLANK())

    Then get the expected result.

    I attach my sample below for reference.

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

smpa01's avatar
smpa01
Community Champion
4 years ago

bmarshall92  "major in any" would follow this

Measure =
CALCULATE (
    COUNTROWS ( t2 ),
    FILTER (
        t2,
        t2[Major1] <> BLANK ()
            || t2[Major2] <> BLANK ()
            || t2[Major3] <> BLANK ()
    )
)

 

Also. you don't need to create any adiitional tables. the pbix is attached.