To Filter out null and 0

[harshagraj]

Hi all i have 3 columns USER_DN, Manager Expectation,Final Rating and i have a measure % gap.
Now i have to differenciate User DN based on gap % if % gap>=0 then "Met", "Not Met"
But I would need to exclude Null and 0 in Manager Expectation & Final Rating.
I tried 

Met_Not Met = CALCULATE(IF([% gap]>=0,"Met","Not Met"),ALLEXCEPT('USER_RATING','USER_RATING'[USER_DN]))
I need Distincount of User_DN for Met and Not Met.

USER_DN	Manager Expectation	Final Rating	% gap
A	0		47%
A	1	1	0%
B	2	3	50%
B			29%
B	4	5	25%
C	0	2	-37%
D	2	0	-65%
E	4		-100%
E			0%

[v-diye-msft · Accepted solution]

Hi @harshagraj 

Kindly let me know if you'd like to get below results:

Pbix attached.

[parry2k]

@harshagraj since you already have a measure for %, try following measure

Met or not met = 
SWITCH ( TRUE(),
 [% Measure] == BLANK() || [% Measure] = 0, BLANK(),
 [% Measure] > 0, "Met",
 "Not Met"
)

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[harshagraj]

Thank you @parry2k  But i want to display Met-Distinctcount & Not Met-Distinctcountof UserDN by excluding nulls and zeros in Manager Expectation and Final Rating. The calculation is getting effected because of nulls and 0. The calcuation i used for % gap is 

(AVERAGE('USER_RATING'[Final_User_Rating])/AVERAGE('USER_RATING'[MANAGER_EXPECTATIONS]))-1.
I can exclude nulls and 0 in query level but other calculations will be effected. I tried something like below but i didnt work.
Met_Not Met =
CALCULATE (
IF ( [% gap] >= 0, "Met", "Not Met" ),
FILTER (
'SA USER_RATING',
NOT ( ISBLANK ( 'SA USER_RATING'[Final_User_Rating] ) )
&& 'SA USER_RATING'[Final_User_Rating] <> 0
&& NOT ( ISBLANK ( 'SA USER_RATING'[MANAGER_EXPECTATIONS] ) )
&& 'SA USER_RATING'[MANAGER_EXPECTATIONS] <> 0
)
)

[parry2k]

@harshagraj did you tried what I suggested? If it didn't worked then explain what is not working and what is your expected output. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490...