Countrows in multiple column based on partial text value

[LaurensM02]

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):

Key	Linked Issues.1	Linked Issues.2	Linked Issues.3	Linked Issues.4	Linked Issues.5	Linked Issues.6	Linked Issues.7
ICB-1	THGRW-123	IDF-210
ICB-2	IPQ-11	THGRW-10	STR-22
ICB-3		THGRW-55
ICB-4				THGRW-55
ICB-5						THGRW-191

[Thejeswar]

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]

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.

[LaurensM02]

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]

@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]

I have a strange problem before I want to start unpivotting columns. The following formula gives me the correct number of rows. But whenever I change it to [Linked Issues.2] or another number, the formula returns a blank.