Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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:
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 |
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.
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.
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.
You can convert your table to look like below using Unpivot Columns option in Power Query Editor
Refer below link
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
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
60 | |
58 |
User | Count |
---|---|
188 | |
111 | |
105 | |
78 | |
71 |