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,...
  • 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] }
    )
    )
    )

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] }
    )
    )
    )