topic countrow when return zero or blank in DAX Commands and Tips

countrow when return zero or blank

sanjanarama — Thu, 03 Feb 2022 10:30:49 GMT

I have a filter and want to display count of rows returned after search. but im not able to figure out how to write condition if blank is returned from count rows

Total Rows = IF(ISFILTERED ('table'[Search]),COUNTROWS('table') & " customer match(es) found.", "No customer match(es) found.")

before any search is performed im displaying below

Re: countrow when return zero or blank

ValtteriN — Thu, 03 Feb 2022 11:22:13 GMT

Hi,

Try using ISBLANK. So something like this:

Total Rows = IF(ISFILTERED ('table'[Search]),

IF(ISBLANK(COUNTROWS('table') ), "No customer match(es) found.",

COUNTROWS('table') & " customer match(es) found.", "No customer match(es) found."))

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

Re: countrow when return zero or blank

sanjanarama — Thu, 03 Feb 2022 13:25:36 GMT

ValtteriN
Too many arguments were passed to the IF function. The maximum argument count for the function is 3.

im getting above error when i use the dax

Re: countrow when return zero or blank

ValtteriN — Thu, 03 Feb 2022 13:47:00 GMT

sanjanarama There was a problem with the brackects. Here is the modified verison:

IF(ISFILTERED ('table'[Search]),

IF(ISBLANK(COUNTROWS('table') , "No customer match(es) found.",

COUNTROWS('table') & " customer match(es) found.", "No customer match(es) found.")))

Re: countrow when return zero or blank

sanjanarama — Thu, 03 Feb 2022 13:51:30 GMT

I'm sorry "Too many arguments were passed to the ISBLANK function. The maximum argument count for the function is 1."

this is error im seeing

Re: countrow when return zero or blank

sanjanarama — Fri, 04 Feb 2022 08:42:17 GMT

In case if anyone is looking for solution, i did figure out

1) first create measure to get total rows and return 0 if no rows

TotalRowsCount = IF (CALCULATE ( COUNTROWS('Table') ) = BLANK (),
0,

CALCULATE ( COUNTROWS('Table') )

)

2) create below measure to get the display
Total Rows = IF( ISFILTERED ('Table'[Search]) ,'Table'[TotalRowsCount] & " customer match(es) found.", "No customer match(es) found." )