Count number of shops or new shops

[lekkerbek]

Hi,

I'm having a table with shopnames and with a date when they were opened and another column with the closing date (or blank when they are still open). For example:

Shop 1, 1 January 2006

Shop 2, 15 March 2009

Shop 3, 1 July 2011

Shop 4, 5 July 2011

etc

etc

I would like to setup a card which shows the new shops in a particular period (for example 2017). I just entered the shopname into the field and selected count. I set the visual level filter to "opened after 31-12-2016.

That works. However, when I use a slicer and filter it by Province it shows "blank" in case when there isn't a new shop.

Makes sense, but how can I change "blank" to 0 in this case? That's better looking I think.

Do I need to create a measure? Should it be something like?

count shopnames where "opened" after 31-12-2016

and enclosed with "if isblank" to change the blank into 0.

Thanks in advance.

[v-qiuyu-msft · Accepted solution]

Hi @lekkerbek,

Currently, the Card visualization will not trace blank value as 0. You need to create a measure like below:

New Measure = IF(ISBLANK(DISTINCTCOUNT([Shop])),0,DISTINCTCOUNT([Shop]))

You can also vote on this same idea: Count should return 0 instead of (Blank).

Best Regards,
Qiuyun Yu

[lekkerbek]

Thanks for your help. Works like a charm.

Also voted for the idea to implement.