Showing '-' for all blanks

[Anonymous]

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.

[v-frfei-msft]

Hi @Anonymous ,

In your secnario, please update your measure as below.

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

For more details, please check the pbix as attached.

[Anonymous]

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]

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.

[Anonymous]

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

[v-frfei-msft]

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.

[Anonymous]

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

[Anonymous · Accepted solution]

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

[Anonymous]

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.

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]))