How to count the number of different values from many columns

[Anonymous]

Hi everyone,

I have a small request about a project I'm doing on PBI Desktop :

I have, in the same table, 3 columns for which I want to count the number of each value.

I don't have access to Query, thus I can't transpose or unpivot the columns.

For instance :

Table 1

       Col 1  |  Col 2  |  Col 3

          A           A           B

          B           B           C

          B           A           B

          A           A           A

For this case, the indicator I want to highlight the most common value : A with a count of 6.

How may I perform that ?

Thanks in advance !

[v-eqin-msft]

Hi @Anonymous ,

According to my understanding, you want to calculate the count of per Category from multiple columns, right?

In my opinion, it is the easiest way to use Slicer to dynamically display the count.

You could use the following formula:

1.Create a new table for slicer

2.Calculate each column’s count:

countPerCategory =
VAR _sele =
    SELECTEDVALUE ( 'forSlicer'[Category] )
RETURN
    CALCULATE (
        COUNT ( CountTable[Col1] ),
        FILTER ( CountTable, CountTable[Col1] = _sele )
    )
        + CALCULATE (
            COUNT ( CountTable[Col2] ),
            FILTER ( CountTable, CountTable[Col2] = _sele )
        )
        + CALCULATE (
            COUNT ( CountTable[Col3] ),
            FILTER ( CountTable, CountTable[Col3] = _sele )
        )

My visualization looks like this:

Is the result what you want? If you have any questions, please upload some data samples and expected output.

Please do mask sensitive data before uploading.

Best Regards,

Eyelyn Qin

[Anonymous]

Hi @v-eqin-msft 

Thanks for your answer, what I want is not to select what category fo which I want the count but directly showing the category having the most frequency.

Thanks !

[lkalawski · Accepted solution]

@Anonymous

Having such a data set, you can create a measure without having to interfere with the data (you wrote that you do not have this possibility).

This measure will give you the most common value in all columns:

The Most Common Category =
VAR _table =
    UNION (
        SELECTCOLUMNS ( 'Table', "Columnas1", 'Table'[Column1] ),
        SELECTCOLUMNS ( 'Table', "Columnas1", 'Table'[Column2] ),
        SELECTCOLUMNS ( 'Table', "Columnas1", 'Table'[Column3] )
    )
VAR _summarize =
    SUMMARIZE (
        _table,
        [Columnas1],
        "Cnt", COUNTX ( FILTER ( _table, [Columnas1] = EARLIER ( [Columnas1] ) ), [Columnas1] )
    )
RETURN
    MAXX ( TOPN ( 1, _summarize, [Cnt] ), [Columnas1] )

_______________
If I helped, please accept the solution and give kudos! 😀

[Anonymous]

@lkalawski 

Thank you very much ! You helped me a lot with that !!

[pranit828]

HI @Anonymous 

DISTINCTCOUNT(
         SELECTCOLUMNS(Table1,"Col1",[Col1],"col2",[Col2],"col3",[Col3])
    )

[Anonymous]

Hi @pranit828 ,

This solution doesn't work because the function DISTINCTCOUNT only take as arguments columns and not tables.

Thanks for your attention

[amitchandak]

@Anonymous , better to unpivot the columns

https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/

[Anonymous]

Hi @amitchandak !

Thanks for your reply. Unpivoting table is performed with PBQ which I can't use now because  I work with imported data on PBI Desktop. 

That's why I was asking for some insights about an other way of doing that, maybe a pure DAX way with a more or less complex measure ?