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
Anonymous
Not applicable

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
Anonymous
Not applicable

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

View solution in original post

8 REPLIES 8
v-frfei-msft
Community Support
Community Support

Hi @Anonymous ,

 

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.
Anonymous
Not applicable

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.

 

 

bharathvee
Frequent Visitor

Hi @Anonymous , 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

Anonymous
Not applicable

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

Hi @Anonymous ,

 

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.
Anonymous
Not applicable

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

Anonymous
Not applicable

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

Anonymous
Not applicable

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

 

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.