Forum Discussion

tdailey77's avatar
tdailey77
Frequent Visitor
6 years ago
Solved

Need to be able to count blank values

Hello,

I recently moved from On-Premise SQL Microsoft Dynamics to D365 online and moving my data source to the online database has been a huge headache.   I don't know why I lose my option set label names and only get values now. Anyway, I have found another headache.  In my old report I could easily build a table to count all campaign responses by their disposition reasons.

Even though my "blanks" don't show a column header, at least I am able to count them.

 

Now in the new report I only get the values of the Disposition Reason-

This still isn't ideal, because now the labels are values not the text that I want to show the users.

I made a new table and put my values and the text, then made a relationship to the values in the campaign response table thinking this would solve my problem, but now I get this result which isn't counting my blanks 😞

Does anyone know how I can get this to work correctly and label at least the columns except blank and I can explain to the users what the blank header is counting?  

 

I tried adding blanks as a value in my new relationship table but since there's no value for blank in my data it's not recognizing them either that way.

 

Thank you

  • Hi tdailey77 ,

     

    Try replacing your BLANK() values with some value like - "Unknown", "Not Known", etc.

    It's always good to clean your data specially if it has missing values like blanks.

     

    Thanks,

    Pragati

4 Replies

  • Hi tdailey77 ,

     

    Try replacing your BLANK() values with some value like - "Unknown", "Not Known", etc.

    It's always good to clean your data specially if it has missing values like blanks.

     

    Thanks,

    Pragati

    • tdailey77's avatar
      tdailey77
      Frequent Visitor

      Thank you Pragati11 , that fixed it!  I forgot that I can do that in Power Bi and leave my dynamics database to have the blank values still, very appreciated!

       

       

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion

    tdailey77 - I like Pragati11 suggestion. I was having trouble following your post. There is a COUNTBLANK function. 

     

    Also see this article for different types of blanks:

    https://xxlbi.com/blog/different-blanks-in-dax/

     

    It would be really helpful if you could supply sample data and expected output to replicate the issue. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    The most important parts are:
    1. Sample data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.

  • Have you already looked into the "Show items with no data" functionality and the way it is impacted by your choice of table for each relationship field?