Forum Discussion

jthomson's avatar
jthomson
Icon for Solution Sage rankSolution Sage
9 years ago
Solved

Count instances of string in multiple columns

Hi,   Been searching the forums but can't find anything that exactly replicates what I'm trying to do - I currently have an Excel file that has the following column names (along with various others...
Need Help
  • Vvelarde's avatar
    Vvelarde
    9 years ago

    jthomson

     

    Hi try with this:

     

    CountinAllHirerColumns =
CALCULATE (
    COUNTROWS ( Table1 ),
    USERELATIONSHIP ( HirerTable[Hirer], Table1[Hirer 1] )
)
    + CALCULATE (
        COUNTROWS ( Table1 ),
        USERELATIONSHIP ( HirerTable[Hirer], Table1[Hirer 2] )
    )
    + CALCULATE (
        COUNTROWS ( Table1 ),
        USERELATIONSHIP ( HirerTable[Hirer], Table1[Hirer 3] )
    )

    After that in your visual filter:

     

     

    Regards

     

    Victor

    Lima - Peru

Vvelarde's avatar
Vvelarde
Icon for Community Champion rankCommunity Champion
9 years ago

jthomson

 

Hi try with this:

 

CountinAllHirerColumns =
CALCULATE (
    COUNTROWS ( Table1 ),
    USERELATIONSHIP ( HirerTable[Hirer], Table1[Hirer 1] )
)
    + CALCULATE (
        COUNTROWS ( Table1 ),
        USERELATIONSHIP ( HirerTable[Hirer], Table1[Hirer 2] )
    )
    + CALCULATE (
        COUNTROWS ( Table1 ),
        USERELATIONSHIP ( HirerTable[Hirer], Table1[Hirer 3] )
    )

After that in your visual filter:

 

 

Regards

 

Victor

Lima - Peru

  • YashGurudath891's avatar
    YashGurudath891
    Regular Visitor
    7 years ago

    Would you happen to have an idea what to do if there were 700+ Columns? In my case, I am looking for specific string in all these columns.
    I can get the Results in excel by doing COUNTIF(A1:A700,"PASS") and it gives me the total count.
    Please let me know if there is a solution to this. Vvelarde 

     

    Thanks in Advance

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      7 years ago

      Hi,

      In your text, you state 700+ columns but in your formula you have 700 rows.  Please clarify.  If there are 700 columns, you may have to Unpivot columns and then it becomes an easy one to solve.

      Share some data and show the expected result.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Vvelarde 

    Hi, adding to that, can this be used for dynamic number of Hirer columns in Table1?