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
jschne
Frequent Visitor

How to get Key Influencers to treat nulls as blanks and not 0

Hi,

 

When we use key influencers (KI), it treats null values as 0. How can we get it to not do this, and why would it do this by default (it seems very misleading)? 

Another side effect of KI treating nulls as zeros is that when we are joining different tables together and KI does binning, we get two "null" bins: "0 or below" (nulls in the table) and "blank" (missing values from that table). Obviously this is really misleading in cases of regression (where you get a bunch of 'zero' data points that are not real), or binning (where you have to repeatedly explain that bins of "0 or below" are missing bins that are not BLANK, but should be treated as BLANK).

 

The only solutions I have found so far while searching is to either get rid of the rows, or put an advanced filter on the visual. The problem is that we have incomplete data that involves many variables so either of these solutions are not great as with every filter/removal we lose a prohibitive amount of data.

 

Ideally, KI would just ignore the data points for the missing values, treating them as blanks whether or not the blanks result from a join or missing data in the table.

 

Thanks!

1 ACCEPTED SOLUTION

Hi @jschne ,

 

In Power BI the Blanks are treated has 0 so in some of the visualizations you will see 0 on others they will not appear. Believe that the case here is where the calculation is made has 0.

 

Regarding the second question has you refer I'm not really sure about that has I refered don't have any KI that has this type of information.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

6 REPLIES 6
MFelix
Super User
Super User

Hi @jschne ,

 

Overall DAX and PBI treats blanks/null as 0 in summary

 

BLANK() = 0 = true

but

ISBLANK(0)= FALSE

 

One way to overcome this you need to add the syntax to your model.

 

NOT ( ISBLANK ( Data[X] ) )

 Again this is to keep the blank has blanks in a calculation don't have any KI with this type of issue so not sure if this relates with your problem.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



jschne
Frequent Visitor

Hi @MFelix ,

 

So if I understand you, the step has to be done in the calculation, not in the data prep? So there's no way to use something's that pre-packaged like KI with blank data accurately?

Hi @jschne ,

 

In Power BI the Blanks are treated has 0 so in some of the visualizations you will see 0 on others they will not appear. Believe that the case here is where the calculation is made has 0.

 

Regarding the second question has you refer I'm not really sure about that has I refered don't have any KI that has this type of information.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



jschne
Frequent Visitor

Hi @MFelix ,

 

Thanks for your help, I wish there was an "Accept as Helpful" button since it doesn't solve my problem, but it seems like it won't be solved. If the behavior is "some of the visualizations you will see 0" then the only option for me would be to create separate tables for each column, removing the blanks, so that KI doesn't see the blank=0, just the join BLANK()... which is a pain and really inconvienient.

 

Attached is what I am struggling with (just an example , but Table 2 X does not have any 0s in it).

 

Untitled.png

Can you share a mockup of the table so I can make some tests?

 

You want to remove the blanks from the chart correct or do you want to present has 0?


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



jschne
Frequent Visitor

Hi @MFelix 

 

This is the simple mockup (hopefully it works since I can't upload pbix files):

https://drive.google.com/file/d/1R1Yc23QDcRZS0JSDC4JGIW-AR1GuZEDW/view?usp=sharing

Ideally, "BLANK" and "0 or below" would be both treated as "BLANK", and this could be done with a measure or something.

The issue is the real data set has several tables with dozens of columns, and excluding (with advanced filters for instance) for one column will exclude the data points of that table for the other columns.

 

Thanks for taking a look

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.