Grouping different columns for counting in a visual

[ORDR]

Hello everyone,

I have a table with 4 columns, an id column and 3 same-kind columns containing some strings.

The purpose is for every kind of string to count how many times it has appeared in any of the 3 columns

Example : 

Id	Col1	Col2	Col3
1	A	B	A
2	B	C	D
3	D	A	B

What I want to get is a visual (for example a table or a clustered bar chart) giving something like :

A	3
B	3
C	1
D	2

That could seem very simple, but the trouble is that in powerBi visual (Bar charts ou matrix) when you add several columns to the data fields (Rows in matrix, Group in Treemap Axis in clustered bar chart), they present the values of these columns separately, in a cartesian product, a concatenation of the 3 columns or a hierarchy. 

You can define "groups" but they gather values of the same column. 

So is it possible to specify an "automatic grouping" of different data columns ? 

Thank you very much if someone has an idea.

[v-easonf-msft]

Hi , @ORDR 

You can take a try to create a new table and build a relationship between tables:

New Table = 
UNION (
    SELECTCOLUMNS ( 'Table', "ID", 'Table'[Id], "Column", 'Table'[Col1] ),
    SELECTCOLUMNS ( 'Table', "ID", 'Table'[Id], "Column", 'Table'[Col2] ),
    SELECTCOLUMNS ( 'Table', "ID", 'Table'[Id], "Column", 'Table'[Col3] )
)

You can check my sample pbix file for more details.

Best Regards,
Community Support Team _ Eason

[ORDR · Accepted solution]

Hello thenk you for ths pbix. I have added several things to you report : 

1. A Date and Country columns to the data table

2. A Calendar table

3. A relationship 1-* between Calendar table and Date column, and a relationship 1-* between Id column in Data table and id in Grouping Table.

4. A bar chart showing how many Id by selected country and a date slicer (by year).

When I select a year only rows with this date are selected and the grouping table is recomputed.

The same occurs when I select only a Country from within the bar chart.

So the content of the UNION table is dynamically filtered through the id column.

How can I upload this report ? It seems I'm not allowed to do that.

[CNENFRNL]

Hi, @ORDR , all you need to do is to unpivot the dataset into a one-dimensional table; then use a simplest measure to count occurrence of each target value.

Count = COUNTROWS( 'Table' )

[ORDR]

Hello CNENFRNL, thank you very much for your answer.

I've already though about that but I'm not sure it gives me what I want. The matter is that my real table has a lot of columns and filters (by slicer or other visuals); is an unpivoted table automatically filtered with all filters applied to main source table ?