Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Count a value in multiple columns

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!! 
Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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. 

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    5 years ago

    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.