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 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_DNManager ExpectationFinal Rating% gap
A0 47%
A110%
B2350%
B  29%
B4525%
C02-37%
D20-65%
E4 -100%
E  0%
Need Help

4 Replies

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