Forum Discussion
Helper IHow do I count distinct values (words) across multiple columns.
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 measureCount =
SUMX (
VALUES ( List[Value] ),
COUNTROWS (
FILTER (
'Table',
List[Value] IN { 'Table'[Column B], 'Table'[Column F], 'Table'[Column G] }
)
)
)
Helper IThank 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?
Community ChampionIf 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] }
)
)
)