Create table containing count of True/false of multiple columns

[Soof1234]

Hi,

I was looking for a similar topic, but couldn't automatically find it. I have like 6 columns with each column has either value true or false. I forgot how to do this as I did it before without unpivotting the columns. So I am looking for the solution without doing unpivot columns:

My Data:

Table is called 'Table'

My desired solution in a table without using unpivot:

	HasExchangeLicense	HasOneDriveLicense	HasSharePointLicense	HasSkypeForBusinessLicense
TRUE	20	80	60	40
FALSE	60	0	20	40

Thanks in advance

Kind Regards,

Soof1234

[v-yadongf-msft · Accepted solution]

Hi  @Soof1234 , 

This is my test table (all columns are text type):

Create a new table:

NewTable = DISTINCT('Table'[ColumnA])

Create measures:

Count of ColA = COUNTX(FILTER(ALL('Table'),'Table'[ColumnA] = MAX('NewTable'[Type])),'Table'[ColumnA])

Count of ColB = COUNTX(FILTER(ALL('Table'),'Table'[ColumnB] = MAX('NewTable'[Type])),'Table'[ColumnB])

Count of ColC = COUNTX(FILTER(ALL('Table'),'Table'[ColumnC] = MAX('NewTable'[Type])),'Table'[ColumnC])

Count of ColD = COUNTX(FILTER(ALL('Table'),'Table'[ColumnD] = MAX('NewTable'[Type])),'Table'[ColumnD])

Create a matrix visual and you will get the result you want:

Please refer the pbix file.

Best regards,

Yadong Fang

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

[v-yadongf-msft · Accepted solution]

Hi  @Soof1234 , 

This is my test table (all columns are text type):

Create a new table:

NewTable = DISTINCT('Table'[ColumnA])

Create measures:

Count of ColA = COUNTX(FILTER(ALL('Table'),'Table'[ColumnA] = MAX('NewTable'[Type])),'Table'[ColumnA])

Count of ColB = COUNTX(FILTER(ALL('Table'),'Table'[ColumnB] = MAX('NewTable'[Type])),'Table'[ColumnB])

Count of ColC = COUNTX(FILTER(ALL('Table'),'Table'[ColumnC] = MAX('NewTable'[Type])),'Table'[ColumnC])

Count of ColD = COUNTX(FILTER(ALL('Table'),'Table'[ColumnD] = MAX('NewTable'[Type])),'Table'[ColumnD])

Create a matrix visual and you will get the result you want:

Please refer the pbix file.

Best regards,

Yadong Fang

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

[Soof1234]

Hi @v-yadongf-msft ,

Thanks a lot! This was the solution I was looking for. Small note, is it also possible to have the row 'Totals' correct as it has the same count as valuue "true".

Kind Regards,

Soof1234

[v-yadongf-msft · Accepted solution]

Hi @Soof1234 ,

Now the total value is correct. Please refer the pbix file:

Best regards,

Yadong Fang

[mittalpatel130]

Hello @v-yadongf-msft ,

Thank you for the solution and I am able to build the Matrix for the number of True/false fields to display all together. 

However, I have other fields from the same table and visualizations on the same report page. When I click on any other visualizations, this Matrix doesn't interact/filter anyway.  I checked in Edit Interactions section, it is all enabled.

Can you please suggest me any alternate DAX that works in this case?

Thank you.

[Soof1234]

@v-yadongf-msft Thanks again!

[Soof1234]

Hi @Jayee, 

Thanks this probably helps, but I only needed the table I desired as a table visual, not as a datatable. I also have a lot of other columns next to the 4 I put in the example which do not need to be unpivoted.

Kind Regards,

Soof1234

[Jayee]

Hi @Soof1234 ,

1.Add an index column transformation in Power Query

2. Select Index column and click "Unpivot Other Columns" in PQ.

3.Select Attribute column and click on Pivot Column in PQ.

select the value and aggregate as shown and ok

Final Result:-

 If this post helps, then please consider Accept it as the solution, Appreciate your Kudos!!