Forum Discussion

gobluemba's avatar
gobluemba
Icon for Helper I rankHelper I
3 years ago
Solved

How do I count distinct values (words) across multiple columns.

Greetings,

I have columns with similar data (e.g. words), and I want to count the TOTAL number frequency of the words across all three columns. So if the world "airplane" occurs 5 times in column B, 6 times in column F, and 9 times in column G, I would get a Total count for the word "airplane" of: 20

 

Is there a formula I can use? 

 

Thanks in advance!

  • tamerj1's avatar
    tamerj1
    3 years ago

    gobluemba 

    If you don't have a list if the unique value you my create it as a calculated table 

    List =
    DISTINCT (
    SELECTCOLUMNS (
    UNION (
    ALLNOBLANKROW ( 'Table'[Column B] ),
    ALLNOBLANKROW ( 'Table'[Column F] ),
    ALLNOBLANKROW ( 'Table'[Column G] )
    ),
    "Value", [Column B]
    )
    )

    make sure no relationships are created automatically. 
    then you an place List[Value] in a table or chart visual along with the following measure 

    Count =
    SUMX (
    VALUES ( List[Value] ),
    COUNTROWS (
    FILTER (
    'Table',
    List[Value] IN { 'Table'[Column B], 'Table'[Column F], 'Table'[Column G] }
    )
    )
    )

3 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi gobluemba 

    let me present two options 

     

    =
    COUNTROWS (
    FILTER (
    'Table',
    'Table'[Column B] = "airplane"
    || 'Table'[Column F] = "airplane"
    || 'Table'[Column G] = "airplane"
    )
    )

     

    =
    COUNTROWS (
    FILTER (
    'Table',
    "airplane" IN { 'Table'[Column B], 'Table'[Column F], 'Table'[Column G] }
    )
    )

    • gobluemba's avatar
      gobluemba
      Icon for Helper I rankHelper I
      3 years ago

      Thank you for the quick response tamerj1 - I guess I wasn't clear, I apologize. I want to count every unique value in each of those three columns - airplane was an example - so I would also want to count "Car" and "train" and every other unique value. That make sense? 

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        3 years ago

        gobluemba 

        If you don't have a list if the unique value you my create it as a calculated table 

        List =
        DISTINCT (
        SELECTCOLUMNS (
        UNION (
        ALLNOBLANKROW ( 'Table'[Column B] ),
        ALLNOBLANKROW ( 'Table'[Column F] ),
        ALLNOBLANKROW ( 'Table'[Column G] )
        ),
        "Value", [Column B]
        )
        )

        make sure no relationships are created automatically. 
        then you an place List[Value] in a table or chart visual along with the following measure 

        Count =
        SUMX (
        VALUES ( List[Value] ),
        COUNTROWS (
        FILTER (
        'Table',
        List[Value] IN { 'Table'[Column B], 'Table'[Column F], 'Table'[Column G] }
        )
        )
        )