Forum Discussion

LaurensM02's avatar
LaurensM02
Regular Visitor
2 years ago

Countrows in multiple column based on partial text value

Dear all,

 

For a project I need to be able to count the number of rows in a file, based on a text value in one of eight defined columns.

I want to count a row when one of the "Linked Issues" columns contains a text value that starts with "THGRW-".

 

I tried using the COUNTROWS function combined with FILTER for only one column, which looks like this:

Episode without SM =
    COUNTROWS(FILTER('Sheet1', 'Sheet1'[Linked Issues.1] >= "THGRW-000"))
 
For multiple columns I wanted to just sum (using the + operator) the countrows functions, but already this function for a single column does not give me a result.

 

The sheet looks something like this (Sheet1):

KeyLinked Issues.1Linked Issues.2Linked Issues.3Linked Issues.4Linked Issues.5Linked Issues.6Linked Issues.7

ICB-1

THGRW-123IDF-210     
ICB-2IPQ-11THGRW-10STR-22    
ICB-3 THGRW-55     
ICB-4   THGRW-55   
ICB-5     THGRW-191 
Need Help
Tips and Tricks

5 Replies

  • Thejeswar's avatar
    Thejeswar
    Super User
    2 years ago

    Hi LaurensM02 ,

    Your DAX won't work. That is because 

    >= operator will work only with numbers. But you are using >= with a string value

    i.e.

    COUNTROWS(FILTER('Sheet1', 'Sheet1'[Linked Issues.1] >= "THGRW-000"))

    In the above  DAX, THGRW-000 is a string and hence cannot be used with a >= operator

     

    You will have to use a == operator or an IN operator to compare text and count. Then sum the different conditions.

     

    If you want more focused answer, pls. share how your output should look.

    • LaurensM02's avatar
      LaurensM02
      Regular Visitor
      2 years ago

      Hi Thejeswar,

       

      Thanks for your quick response. I am using the measure for a card visual that shows the total number of rows that meet the filter conditions. So the output should eventually be a number.

       

      Also I tried using a wildcard character in the text value, since the filter needs to count every row where one of the Linked Issues columns contain a value that starts with "THGRW-", but I could not get that working.

      • Thejeswar's avatar
        Thejeswar
        Super User
        2 years ago

        LaurensM02 ,

        You can convert your table to look like below using Unpivot Columns option in Power Query Editor

         

        Refer below link

        Unpivot Columns in Power BI 

         

        Post that you can use the below DAX to get the ouput that you were expecting. In this case there are 5 combinations having "THGRW" in them

         

    • LaurensM02's avatar
      LaurensM02
      Regular Visitor
      2 years ago

      I also tried the following formula, but this results in a blank result when added to a card visual. I don't know what's wrong.