Count Multiple Choice Text field occurrences from Multiple Columns.

[RooneyHat]

Hi Everyone,

Apologies if this has been answered before, I'm new to PowerBi and I haven't been able to find a solution.

I have a SharePoint list and am attempting to count every text occurrence (20+ options) from multiple columns. This is filtered by year and quarter column: '2021 Q2'.

	Objective Choice 1	Objective Choice 2	Objective Choice 3	Objective Choice 4	Objective Choice 5
Person A	Orange	null	Blue	Blue	Green
Person B	null	null	Orange	Orange	Orange
Person C	Blue	Green	Yellow	Yellow	Orange
Person D	Green	Orange	Yellow	Green	null

The goal is to get the count of all text fields that match, there are over 20 possible options:
Orange = 6

Blue = 3

Green = 4

etc.

Thanks in advance for any help/direction!

[v-luwang-msft · Accepted solution]

Hi  @RooneyHat ,

You could try the following steps:

Step 1,create a slicer table:

Step 2,use the following measure on base table:

sum = 
CALCULATE (
    COUNT ( 'Table'[Objective Choice 1] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Objective Choice 1] = SELECTEDVALUE ( Slicer[Color] )
    )
)
    + CALCULATE (
        COUNT ( 'Table'[Objective Choice 2] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Objective Choice 2] = SELECTEDVALUE ( Slicer[Color] )
        )
    )
    + CALCULATE (
        COUNT ( 'Table'[Objective Choice 3] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Objective Choice 3] = SELECTEDVALUE ( Slicer[Color] )
        )
    )
    + CALCULATE (
        COUNT ( 'Table'[Objective Choice 4] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Objective Choice 4] = SELECTEDVALUE ( Slicer[Color] )
        )
    )
    + CALCULATE (
        COUNT ( 'Table'[Objective Choice 5] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Objective Choice 5] = SELECTEDVALUE ( Slicer[Color] )
        )
    )

Then create visualization:

You could download my pbix file to learn more details,wish it is helpful for you!

Best Regards

Lucien

[RooneyHat]

Thanks this helped!

[RooneyHat]

Guess this is more complicated than I thought 🙂