PowerBI - How to count same values from multiple columns

Ramiro 0
Viewed 31

This is my first question here. This time I want to count values that appear in different columns. Each one corresponds to the values from a row, but there is only 1 column that they have in common (not shown in the picture). I need a measure to show a count of each word described in those cells. For example, in this case (please ignore blanks, it's a test), the measure should give a count for the word 'Desodorante' as 3, 'Cabello' as 2, and the rest 1. These words are pre defined and there are no random values accepted. I may say that I want to state each of these words as a kind of category. I would like to make a slicer out of them too.

example

I believe a workaround is to create a calculated table, stating as columns each of these values and allocating a count of each value from these 4 columns?

powerbi-desktop powerbi
1 Answers

  1. A Power Query solution to this problem has been posted here: PowerBI - Count instances of string in multiple columns

  2. A DAX solution to this problem would be to create a new calculated table by appending the 4 columns using UNION() and SELECTEDCOLUMN() and then getting the count via SUMARIZE():

The 2 calculated Tables:

Mesa = 
FILTER(
    UNION(
        SELECTCOLUMNS(
            'Ramiro',
            "Producto", 'Ramiro'[Producto]
        ),
        SELECTCOLUMNS(
            'Ramiro',
            "Producto", 'Ramiro'[Producto2]
        ),
        SELECTCOLUMNS(
            'Ramiro',
            "Producto", 'Ramiro'[Producto3]
        ),
        SELECTCOLUMNS(
            'Ramiro',
            "Producto", 'Ramiro'[Producto4]
        )
    ),
    [Producto] <> BLANK()
)

Producto Count = 
SUMMARIZE(
    'Mesa',
    'Mesa'[Producto],
    "Count", COUNT('Mesa'[Producto])
)
userhiPt0
Related