topic Re: Count rows if more than 0.00 for multiple rows (considering different columns) in DAX Commands and Tips

Count rows if more than 0.00 for multiple rows (considering different columns)

SYChan_22 — Wed, 03 Nov 2021 20:35:29 GMT

Hi Team, I am new to Power BI. I am currently looking for a solution on DAX measure. I would like to count the rows if the column Far (Left) OR Far (right) is more than 0.00 OR the column Near (Left) OR Near (right) is more than 0.00. See attached screenshot. The desired results that fit the condition would be 5, if count manually. I have tried several other formulas but it never work. Desperately looking for solution here. Thanks in advance. Cheers!

Re: Count rows if more than 0.00 for multiple rows (considering different columns)

smpa01 — Wed, 03 Nov 2021 20:44:14 GMT

SYChan_22 try this

MEASURE= COUNTROWS(FILTER(SUMMARIZE(tbl,tbl[Far(left)],tbl[Far(right)],tbl[Near(left)],tbl[Near(right)]), tbl[Far(left)]>0||tbl[Far(right)]>0||tbl[Near(left)]>0||tbl[Near(right)]>0))

Re: Count rows if more than 0.00 for multiple rows (considering different columns)

SYChan_22 — Thu, 04 Nov 2021 13:16:29 GMT

Hi smpa01 , thanks for your suggestion. I tried and it did not work ;( Is there any way to share the sample data here? I could not find any option to attach a file here. Thanks in advance for your help!

Re: Count rows if more than 0.00 for multiple rows (considering different columns)

smpa01 — Thu, 04 Nov 2021 13:17:37 GMT

SYChan_22 you can share it through google drive / 1 drive

Re: Count rows if more than 0.00 for multiple rows (considering different columns)

SYChan_22 — Thu, 04 Nov 2021 13:23:32 GMT

smpa01, as suggested. Here is the link:

https://drive.google.com/drive/folders/1wobXwSza7l1xPcMbYJJ-OpPdbwkYkmF4?usp=sharing

Re: Count rows if more than 0.00 for multiple rows (considering different columns)

smpa01 — Thu, 04 Nov 2021 13:34:01 GMT

SYChan_22

Measure = COUNTROWS ( FILTER ( SUMMARIZE ( tbl, tbl[Prescription: Far left], tbl[Prescription: Far right], tbl[Prescription: Near left], tbl[Prescription: Near right] ), CONVERT ( tbl[Prescription: Far left], DOUBLE ) > 0 || CONVERT ( tbl[Prescription: Far right], DOUBLE ) > 0 || CONVERT ( tbl[Prescription: Near left], DOUBLE ) > 0 || CONVERT ( tbl[Prescription: Near right], DOUBLE ) > 0 ) )

Re: Count rows if more than 0.00 for multiple rows (considering different columns)

SYChan_22 — Thu, 04 Nov 2021 13:49:01 GMT

That works perfectly, smpa01 and the solution saves me a lot of time!

Probably last question from my side: What if I would like to count the rows if it more than 0 and less than 0? In other words, if all the rows contain zero, it shoud not be counted. Thanks!

Re: Count rows if more than 0.00 for multiple rows (considering different columns)

smpa01 — Thu, 04 Nov 2021 13:57:54 GMT

SYChan_22

In other words, if all the rows contain zero, it shoud not be counted - currently the code is doing that.

With the following

CONVERT ( tbl[Prescription: Far left], DOUBLE ) > 0 || CONVERT ( tbl[Prescription: Far right], DOUBLE ) > 0 || CONVERT ( tbl[Prescription: Near left], DOUBLE ) > 0 || CONVERT ( tbl[Prescription: Near right], DOUBLE ) > 0

the code can't return any rows that will have column1=0 and column2=0 and column3=0 and column4=0.

Because DAX is explicitly asked to perform a serach and return a table where any of these 4 columns are >0, therfore the opposite can't be true.

Re: Count rows if more than 0.00 for multiple rows (considering different columns)

SYChan_22 — Thu, 04 Nov 2021 14:15:26 GMT

Thanks smpa01 for your clarification. But I realized the following was not displayed in the result (see screenshot). I would like the rows to be counted as well if it has negative value (or less than zero). Is there anything we can tweak on the DAX measure?

Re: Count rows if more than 0.00 for multiple rows (considering different columns)

smpa01 — Thu, 04 Nov 2021 14:19:01 GMT

SYChan_22 weap in ABS

Measure = COUNTROWS ( FILTER ( SUMMARIZE ( tbl, tbl[Prescription: Far left], tbl[Prescription: Far right], tbl[Prescription: Near left], tbl[Prescription: Near right] ), ABS(CONVERT ( tbl[Prescription: Far left], DOUBLE )) > 0 || ABS(CONVERT ( tbl[Prescription: Far right], DOUBLE )) > 0 || ABS(CONVERT ( tbl[Prescription: Near left], DOUBLE )) > 0 || ABS(CONVERT ( tbl[Prescription: Near right], DOUBLE )) > 0 ) )

Re: Count rows if more than 0.00 for multiple rows (considering different columns)

SYChan_22 — Thu, 04 Nov 2021 14:37:04 GMT

Dear smpa01, I performed the DAX measure. It works fine but it does not match the final results that I imagine. I realized the rows that has repetitions are not included. I would like to count the rows, even if it repeats (see screenshot). Any idea how to modify the DAX measure? Thanks a lot.

Re: Count rows if more than 0.00 for multiple rows (considering different columns)

smpa01 — Thu, 04 Nov 2021 14:44:45 GMT

SYChan_22 add an index column in the data and then run the measure

Re: Count rows if more than 0.00 for multiple rows (considering different columns)

SYChan_22 — Thu, 04 Nov 2021 17:03:17 GMT

Hi smpa01 , as suggested, I performed the following steps: Transform Data -> Add column -> Index column (From 1) -> Close and apply -> Add new measure -> Paste in the DAX. However, 0.00 appeared in the table and counted as 1 row. Have I missed out some steps here? Maybe you can help me on this? Appreciate for your further advice. Cheers

Re: Count rows if more than 0.00 for multiple rows (considering different columns)

smpa01 — Thu, 04 Nov 2021 17:30:20 GMT

SYChan_22 show the index in the matrix and use this as a measure

Measure = COUNTROWS ( FILTER ( tbl, ABS(CONVERT ( tbl[Prescription: Far left], DOUBLE )) > 0 || ABS(CONVERT ( tbl[Prescription: Far right], DOUBLE )) > 0 || ABS(CONVERT ( tbl[Prescription: Near left], DOUBLE )) > 0 || ABS(CONVERT ( tbl[Prescription: Near right], DOUBLE )) > 0 ) )

Re: Count rows if more than 0.00 for multiple rows (considering different columns)

SYChan_22 — Thu, 04 Nov 2021 17:41:54 GMT

Hi smpa01 , thanks for the valuable tips. The row that contains 0.00 isn't shown in the table anymore. As a final step, I would like to now count the rows in the index column in the matrix. How could we achieve this?

Re: Count rows if more than 0.00 for multiple rows (considering different columns)

smpa01 — Thu, 04 Nov 2021 17:45:33 GMT

SYChan_22 I think the quetion is getting sidetracked now and you are asking me different quetion. Did I manage to give you the answer to your original question? I like to think yes and please accept the answer and create a new question.

Re: Count rows if more than 0.00 for multiple rows (considering different columns)

SYChan_22 — Thu, 04 Nov 2021 17:56:36 GMT

Hi smpa01, referring to my original question, I would like to count the rows that meet the abovementioned conditions (yes, the solution worked!). Ultimately, I would like to show the total number of rows that met the conditions in the card visual instead of showing them in the matrix. Sorry for the misunderstanding and I hope I expressed it clear enough now.

Re: Count rows if more than 0.00 for multiple rows (considering different columns)

smpa01 — Thu, 04 Nov 2021 18:02:22 GMT

SYChan_22 did you actually really try putting the same measure in a card to see whether it works?

Re: Count rows if more than 0.00 for multiple rows (considering different columns)

SYChan_22 — Thu, 04 Nov 2021 18:07:40 GMT

Hi smpa01 , the DAX (screenshot) in your previous message was not displayed the first time I read your message. Must be due to the weak internet connection here. Greetings from Kinshasa. Thanks for your expertise!