Forum Discussion

harshagraj's avatar
harshagraj
Icon for Post Partisan rankPost Partisan
6 years ago
Solved

To Filter out null and 0

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 t...
Need Help
parry2k's avatar
parry2k
Icon for Super User rankSuper User
6 years ago

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's avatar
harshagraj
Icon for Post Partisan rankPost Partisan
6 years ago

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