cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
afbraga66
Helper III
Helper III

COUNT/COUNTROWS return 0 instead of blank

Hello,

 

I am currently have an issue with count/countrows. When they evaluate and return nothing it shows blank instead of 0.

I have 2 tables, a calendar table and a fact table with 10 rows.

afbraga66_0-1657876816753.png

I am trying to calculate the percentage of colum ORIT = "Y".

Lines ORIT2 = 
var _orit = CALCULATE(COUNTROWS(dataVN),dataVN[ORIT]="Y")

return if(ISBLANK(_orit),0,_orit)

The result is this. I don't want those extra lines that mean nothing.

afbraga66_2-1657876954162.png

The manufacturing data field comes from the calendar table.

How can I avoid this situation and get the desired result?

 

The .pbix file is in this link if you want to try - https://we.tl/t-bHGwtdUf5d


Thank you.

André

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @afbraga66 
Please try https://www.dropbox.com/t/Fm7RwOsIaqz2Duog

1.png

 

Lines ORIT2 =
IF (
    NOT ISEMPTY ( dataVN ),
    COALESCE ( CALCULATE ( COUNTROWS ( dataVN ), dataVN[ORIT] = "Y" ), 0 )
)

 

View solution in original post

4 REPLIES 4
vapid128
Solution Specialist
Solution Specialist

CALCULATE(COUNTROWS(dataVN),dataVN[ORIT]="Y") + 0
tamerj1
Super User
Super User

Hi @afbraga66 
Please try https://www.dropbox.com/t/Fm7RwOsIaqz2Duog

1.png

 

Lines ORIT2 =
IF (
    NOT ISEMPTY ( dataVN ),
    COALESCE ( CALCULATE ( COUNTROWS ( dataVN ), dataVN[ORIT] = "Y" ), 0 )
)

 

HI @tamerj1 , It works thank you so much! Still, I'm intrigued on what is the role of ISEMPTY() and why without it, it doesn't work. Could you explain, please? Thank you.

@afbraga66 

If the table itself is empty in the current  filter context then you need to keep the result blank. You need to have it zero if only the filtered table by CALCULATE is blank. Just pay attention to the difference between the original table and the filtered table. 

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors