Count combinations of different columns that contain values

[Anonymous]

I have this example data set and want to be able to create a table which has the combinations of all the different columns

Col1	Col2	Col3	Col4
True	True	True
	True	True	True
True	True	True	True
True
	True	True	True
	True	True

The required output would be:

Combinations	Count
Col1, Col2, Col3	1
Col2, Col3, Col4	2
Col1, Col2, Col3, Col4	1
Col1	1
Col2, Col3	1

Is there a way I can do this using PowerBI?

[Anonymous · Accepted solution]

Hi @Anonymous ,

In addition to Greg_Deckler 's reply, after you do some transformation in Power Query Editor. I think your table will look like as below.

Then you can create calculated columns to achieve your goal.

Combinations =
CONCATENATEX (
    FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) ),
    'Table'[Attribute],
    ","
)

Create a table visual by [Combinations] column and distinct count index column.

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

This is perfect thank you. Is there a way of having a combination of "Empty", if there is no value for Col1, Col2, Col3 or Col4?

[Greg_Deckler]

@Anonymous Probably best to add an Index in Power Query Editor and then unpivot your 4 columns. Then this is a much easier solve I think.