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) multip...
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.

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.