Count a value in multiple columns

[Anonymous]

Hi guys! 

After I search all community to get help, I decided to write here. (sorry but I can't send the files)

Basically I need to count every single value in OPT column from twenty (20) multiple columns! 

Just to explain better:

1) I have a sheet with all options code listed in one column "OPT" (see image)

File that I have all options listed in first column

2) I have a BASE excel file that is generated by one system, which means the number of columns is fixed and that I will have the same twenty columns with options code in it (see in yellow)

BASE file with the twenty columns with all options3) Example what I used to do in Excel: In excel I simply do a countif and add the 20 columns returning the number of times that the option code appears (see the loooong list of the formula bar in excel)

Excel file that I have the "COUNTIF" formulas

4) Final result (what I expected to see in PowerBI)

Final result (what i need)

Please, let me know if you guys need more information to help me! 

Thanks in advance!! 

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Base:

Table:

You may create a calculated column or a measure as below.

Calculated column:

Count Column = 
COUNTROWS(
    FILTER(
        UNION(
            SELECTCOLUMNS(Base,"Option Code 1",Base[Option Code 1]),
            SELECTCOLUMNS(Base,"Option Code 2",Base[Option Code 2]),
            SELECTCOLUMNS(Base,"Option Code 3",Base[Option Code 3])
        ),
        [Option Code 1]=EARLIER('Table'[OPT])
    )
)

Measure:

Count Measure = 
COUNTROWS(
    FILTER(
        UNION(
            SELECTCOLUMNS(Base,"Option Code 1",Base[Option Code 1]),
            SELECTCOLUMNS(Base,"Option Code 2",Base[Option Code 2]),
            SELECTCOLUMNS(Base,"Option Code 3",Base[Option Code 3])
        ),
        [Option Code 1]=SELECTEDVALUE('Table'[OPT])
    )
)

Result:

Best Regard

Allan

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

[Anonymous]

Do the 20 column names differ each time the system makes an excel file? Else you could let Power BI put it all the columns under eachother to make one column and count that.