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

[SYChan_22]

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!

[SYChan_22]

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!

[smpa01]

@SYChan_22  you can share it through google drive / 1 drive

[SYChan_22]

@smpa01, as suggested. Here is the link: 

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

[smpa01]

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

[SYChan_22]

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!

[smpa01]

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

[SYChan_22]

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?

[smpa01]

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

[SYChan_22]

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.

[smpa01]

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

[SYChan_22]

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

[smpa01 · Accepted solution]

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

[SYChan_22]

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?

[smpa01]

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

[SYChan_22]

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. 

[smpa01]

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

[SYChan_22]

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! 

[smpa01]

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