cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
airwolf Frequent Visitor
Frequent Visitor

Showing '-' for all blanks

Hi,

I have a column with values against a city. 

So the city is broken down row wise and the the months for a fiscal year as a column. The measure brings in the values. 

I have values for only one month so I require to show '-' agaist all the nulls/empty values.

I wrote the following. 

Stock Prod Finalised = IF(ISBLANK(SUM(Value)), "-", SUM(Value))
 
This works fine, but it brings up all the cities (along with cities which are not relevant)
 
If I bring the value column as is, it simply brings in only 5 cities (which is what I want).
 
I am not sure why the same value column is acting differently.
 
Any insight is appreciated.
 
1 ACCEPTED SOLUTION

Accepted Solutions
airwolf Frequent Visitor
Frequent Visitor

Re: Showing '-' for all blanks

This issue was resolved. The problem was there was a missing relationship.

View solution in original post

8 REPLIES 8
bharathvee Frequent Visitor
Frequent Visitor

Re: Showing '-' for all blanks

Hi @airwolf , can you post a sample of your data and expected results.

 

According to your DAX, it will display the stock values as "-" for null values. 

 

From your description and measure, Power BI will display all the cities like this:

 - cities with values -> City | SUM (Stock Value)

- cities with null values -> City | "-"

 

If you do not want to display the items without data, you can simply turn it off by unchecking "Show items with no data" under values as shown in the attached picture.image.png

Community Support Team
Community Support Team

Re: Showing '-' for all blanks

Hi @airwolf ,

 

In your secnario, please update your measure as below.

Measure = 
IF (
    MAX ( 'Table'[date] ) <> BLANK ()
        && ISBLANK ( SUM ( 'Table'[value] ) ),
    "-",
    SUM ( 'Table'[value] )
)

Capture.PNG

For more details, please check the pbix as attached.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
airwolf Frequent Visitor
Frequent Visitor

Re: Showing '-' for all blanks

Hi Mate,

Thanks for the reply. The DAX you provided helps keep the number of cities the same. However, the "-" shows up only on the total row across all months. The "-" doesn't display against each city nor across the months. Very strange.

 

I've actually written the SQL in the backend to show zero for all empty values. but the zeros are showing up only for the month with valid values.

 

 

airwolf Frequent Visitor
Frequent Visitor

Re: Showing '-' for all blanks

I tried this mate but it brings in heaps of unwanted stuff

Community Support Team
Community Support Team

Re: Showing '-' for all blanks

Hi @airwolf ,

 

Kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
airwolf Frequent Visitor
Frequent Visitor

Re: Showing '-' for all blanks

The data is confidential, so i cannot share it.
I did try to get a trimmed screenshot and attach it, but the forum post complained and removed it automatically.

The below screenshot is using the measure you sent over. As you can see, hard coded zeros from the database are showing up only on Nov along with other values.

powerBi.JPG

 

The below is what I actually want as the output. But as you can see, it's listing all the projects. 

The DAX used for below output.

Measure = IF (
sum('Stock Production'[Stock Production Finanlised])<=0, "-", sum('Stock Production '[Stock Production Finanlised]))

 

powerBi1.JPG

 

airwolf Frequent Visitor
Frequent Visitor

Re: Showing '-' for all blanks

The table in the data model is showing the zeros but it's just not displaying on the front end.

airwolf Frequent Visitor
Frequent Visitor

Re: Showing '-' for all blanks

This issue was resolved. The problem was there was a missing relationship.

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)