cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mstone3
Helper II
Helper II

Displaying Zero instead of Blank

Hello,

 

I have a matrix visual that contains blank values and I would like to show zero instead of blank.  I have tried adding +0 to the end of the measure, as well as the IF(ISBLANK) logic, and both of these work, however it generates another problem.  The page with the matrix visual is synched with a slicer from another page, and when replacing blank with zero using either of the two options, the synced slicer no longer filters the page.  I need to maintain the sync.  Can't seem to determine why this is occuring.

 

Thank you!

1 ACCEPTED SOLUTION
v-luwang-msft
Community Support
Community Support

Hi @mstone3 ,

Since I don't have your specific data, I created a similar template according to your description to present the same problem, my solution is based on my template, you can adjust it according to your specific data, combined with my solution to solve the problem:

Base data:

vluwangmsft_0-1627538965975.pngvluwangmsft_1-1627538973239.png

matrix:(because I click show items with no data,so it show all data now) 

 

Measure2 = CALCULATE(COUNTROWS(Table2),FILTER(Table2,Table2[MatchID]=MAX('Table'[AreaID])))

 

vluwangmsft_2-1627539022244.png

vluwangmsft_3-1627539060590.png

When I set if measure2=blank ,then 0 , it will show like below:

vluwangmsft_4-1627539173849.png

 

Then ,I try the following steps:

create a new table :

 

t3 = VALUES(Table2[Name])

 

vluwangmsft_5-1627539225390.png

Step 2, Use the below measure:

 

M1 = 
VAR result =
    CALCULATE (
        COUNTROWS(  Table2),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[AreaID] IN VALUES ( Table2[MatchID] )
               )
    )
RETURN
    IF([Measure2]=BLANK()&&result<>BLANK(),0,[Measure2])

 

 

Final get (what you want)

vluwangmsft_6-1627539299184.png

 

 

 

Wish it is helpful for you!

 

 

Best Regards

Lucien

View solution in original post

8 REPLIES 8
v-luwang-msft
Community Support
Community Support

Hi @mstone3 ,

Since I don't have your specific data, I created a similar template according to your description to present the same problem, my solution is based on my template, you can adjust it according to your specific data, combined with my solution to solve the problem:

Base data:

vluwangmsft_0-1627538965975.pngvluwangmsft_1-1627538973239.png

matrix:(because I click show items with no data,so it show all data now) 

 

Measure2 = CALCULATE(COUNTROWS(Table2),FILTER(Table2,Table2[MatchID]=MAX('Table'[AreaID])))

 

vluwangmsft_2-1627539022244.png

vluwangmsft_3-1627539060590.png

When I set if measure2=blank ,then 0 , it will show like below:

vluwangmsft_4-1627539173849.png

 

Then ,I try the following steps:

create a new table :

 

t3 = VALUES(Table2[Name])

 

vluwangmsft_5-1627539225390.png

Step 2, Use the below measure:

 

M1 = 
VAR result =
    CALCULATE (
        COUNTROWS(  Table2),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[AreaID] IN VALUES ( Table2[MatchID] )
               )
    )
RETURN
    IF([Measure2]=BLANK()&&result<>BLANK(),0,[Measure2])

 

 

Final get (what you want)

vluwangmsft_6-1627539299184.png

 

 

 

Wish it is helpful for you!

 

 

Best Regards

Lucien

View solution in original post

Hi,could you help me for acheving same requirement by using one table for calculating sum of amount.

Hi @naveen4392 ,

You could create a new case ,and provide your details .Then an engineer will follow up on your problem.

 

Best Regards,

Lucien

Thank you so much for taking the time to reconstruct this problem and identify a solution - I adapted this to my model and it works!  Again, really appreciate your time!

lbendlin
Super User
Super User

None of what you describe requires a measure (for example the criticality of an employee doesn't change if the report viewerapplies filters).  You can consider transforming your data as needed in Power Query, or even further upstream in the source system.

mahoneypat
Super User
Super User

To do that, you first need to check if other FY have counts for that occupation in a variable like below.  Adapt it with your actual table/column names.

 

EmployeeCount =
VAR countallFY =
    CALCULATE ( COUNTROWS ( Employees )ALL ( Employees[FY] ) )
RETURN
    IF ( countallFY > 0COUNTROWS ( Employees ) + 0BLANK () )

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


lbendlin
Super User
Super User

The general recommendation is to use COALESCE() for these scenarios (and yes, it is basically syntax sugar for IF(ISBLANK()) but but etc.). 

 

A measure should not influence a slicer. Did you mean to say calculated column?

Hello and thanks very much for your reply!  I tried COALESCE but wasn’t successful.  I have below some more details of the case and examples of the issue.  Thanks so much for any thoughts you might have. (apologies for the length but wanted to include as much detail as possible).

 

I have a (fictitious) company with 60 employees located in 5 regions (Midwest, Northeast, Pacific, and Southwest).  Each employee holds an occupational type (such as chemist, auditor, geologist, truck driver, etc.).  Across the entire company, there are 18 different occupational types.

 

Additionally, each region considers some of the occupations as critical and others as non-critical and the critical vs. non-critical occupation types vary by region.  If the occupation is critical for a particular region, the occupational title (e.g. chemist) should appear in the visual and if the occupation is non-critical, the generic title ‘Non-Critical’ should appear instead of the occupational title. 

 

To accomplish this, my PowerBI model has two related tables – employee list (dimension table/many) and occupation list (fact table/one).  Each employee on the employee list has a match code that is related to the match code on the occupation list to determine if the occupation is critical or non-critical for that employee’s region.  If the occupation is critical, the related field (that will be used on the row field of the visual will be the occupational title.  If non-critical, the related field will be the generic title ‘Non-Critical’. 

Here’s an example of three records from the employee list fact table:

 

mstone3_0-1627135065169.png

 

And here’s an example of some records from the occupational list dimension table:

 

mstone3_1-1627135065173.png

 

The purpose of the visual is to show the count of employees onboard at two points in time (called FY20 and FY21) by occupational type with a slicer to filter by region. 

 

The employee count is produced using the measure =COUNTROWS(Employee List) 

 

Everything works great at this point.  Here is an example of the visual filtered to Midwest, which correctly shows the Midwest Region’s 10 critical occupations broken out by occupational title and the employee counts.  (non-critical count also correctly shown)

mstone3_2-1627135065176.png

 

And as a second example, here is the view filtered to the Pacific Region showing the Pacific’s 3 critical occupations (non-critical also correctly show):

mstone3_3-1627135065179.png

 

My only goal with this visual is to display zero instead of a blank for those cases where there are no employees.  When I modify the measure to:

 

=COUNTROWS(Employee List) + 0

 

I get the following result (filtering to Midwest for example):

mstone3_4-1627135065182.png

 

So, the result is that the formula did replace the blanks with zeros, but now all the entire company’s 18 critical occupations are displayed and not just the 10 for the Midwest.  The counts are still correct for the Midwest, but I only want to show the Midwest occupations as they were appearing correctly before I added +0 to the measure.  If I try to simply filter them out at the visual level, then they will stay filtered when I switch region where they should be unfiltered.

 

It seems the behavior is that a blank being replaced by a value (0) means that when there is a combination for which there is no data (such as Midwest/Chemist), the visual will still show 0 as a result for that combination.

 

I’m looking for anything I can do to replace blanks with zero and not displace the occupation types that don’t apply for the region.  I would appreciate any assistance as I’ve been thinking about this for hours and have hit a wall.

Thank you!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.