Forum Discussion

RooneyHat's avatar
RooneyHat
Frequent Visitor
5 years ago
Solved

Count Multiple Choice Text field occurrences from Multiple Columns.

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 1Objective Choice 2Objective Choice 3Objective Choice 4Objective Choice 5
Person AOrangenullBlueBlueGreen
Person BnullnullOrangeOrangeOrange
Person CBlueGreenYellowYellowOrange
Person DGreenOrangeYellowGreennull

 

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!

Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    5 years ago

    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

3 Replies

  • v-luwang-msft's avatar
    v-luwang-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    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's avatar
    RooneyHat
    Frequent Visitor
    5 years ago

    Guess this is more complicated than I thought ğŸ™‚