Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DRossi
Helper III
Helper III

Result is (blank) for a specific number

Hi all

 

I have a formula that generates a percentage measure.

 

 

Customer Promise Rate = VAR COUNTOFJOBS = 
   DISTINCTCOUNT('Query1'[FA_ID])

    VAR COUNTOFBROKENPROMISES = 
    CALCULATE(DISTINCTCOUNT('Query1'[FA_ID]),
    'Query1'[Customer Promise] IN {"1"})

    RETURN
   
IF(NOT ISBLANK (COUNTOFJOBS),
     1 -
    DIVIDE(COUNTOFBROKENPROMISES, COUNTOFJOBS))

 

 

I then have a nested IF formula that tell me if the result is within a range it gives an output such as 'Outstanding'

 

CP Ratings = 
  IF(
                        ISBLANK([Customer Promise Rate]), "Not Eligible",
IF(
    [Customer Promise Rate] <= 1 && [Customer Promise Rate] >= 0.95,
     "Outstanding",
     IF(
         [Customer Promise Rate] <= 0.94
         && [Customer Promise Rate] >= 0.90,
         "Exceeds Expectations",
          IF(
              [Customer Promise Rate] <= 0.89
              && [Customer Promise Rate] >=0.85,
              "Acheiving Expectations",
               IF(
                   [Customer Promise Rate] <= 0.84
                   && [Customer Promise Rate] >= 0,
                   "Below Expectations"
               )
           )
       )
))

 


For some reason, that I can't figure out, if it results at 80% then the card shows (blank) instead of 'Below Expectations'. It works some times but then not others dependent on the slicers selected

6 REPLIES 6
v-juanli-msft
Community Support
Community Support

Hi @DRossi 

As tested with your measures, it works on my side.

Capture3.JPG

Could you show me an example or some sample data?

Is there any other tables used and what's relationship between them/

 

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks @v-juanli-msft 

 

I have one relationship so not sure how it's impacting it.

 

So using the formula below. FA_ID is just long numbers such as 312967463. Customer Promise is an IF calculated column that determines whether the time in one column is later than another column resulting in 1 for true 0 for false.

 

Customer Promise Rate = VAR COUNTOFJOBS = 
   DISTINCTCOUNT('Query1'[FA_ID])

    VAR COUNTOFBROKENPROMISES = 
    CALCULATE(DISTINCTCOUNT('Query1'[FA_ID]),
    'Query1'[Customer Promise] IN {"1"})

    RETURN
   
IF(NOT ISBLANK (COUNTOFJOBS),
     1 -
    DIVIDE(COUNTOFBROKENPROMISES, COUNTOFJOBS))

 

datatable.PNG

Hi @DRossi 

Still can't reproduce this problem.

Capture2.JPG

You could check the measures with formula partly, for example

measure1=IF(
                   [Customer Promise Rate] <= 0.84
                   && [Customer Promise Rate] >= 0,
                   "Below Expectations"
               )

 

 

 

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
amitchandak
Super User
Super User

You have used

IF(NOT ISBLANK (COUNTOFJOBS),
     1 -
    DIVIDE(COUNTOFBROKENPROMISES, COUNTOFJOBS))

Can you change it to

IF(NOT (ISBLANK (COUNTOFJOBS)),
     1 -
    DIVIDE(COUNTOFBROKENPROMISES, COUNTOFJOBS),0)

Hi @amitchandak 

Tried it but it doesn't work, it stays as (blank) and it also does something weird to my graph, instead of what is seleceted on the date slicer, it pulls every date.

 

 

 

Can you share sample data and sample output. If possible please share a sample pbix file after removing sensitive information.Thanks.

My Recent Blog -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.