topic Set blank and set zero properly when drawing line chart in DAX Commands and Tips

Set blank and set zero properly when drawing line chart

Mackie — Tue, 17 Mar 2020 07:28:42 GMT

I want to create a line chart which has "date" as X-axis and count of "product_key" as Y-axis.
And blank should be zero. So I create this measure but unfortunately it reports zero when date comes over limit such as 2019/10/1 and 2020/1/1. How can I set BLANK() when date comes over limit? Thank you.

date	product_key
2019/11/1	1
2019/11/1	2
2019/12/1	2
2019/10/1	3
2019/11/1	3
2019/12/1	3

count_product_key = IF ( CALCULATE ( COUNT('table'[product_key]) ) = BLANK (), 0, CALCULATE ( COUNT('table'[product_key]) ) )

Re: Set blank and set zero properly when drawing line chart

az38 — Tue, 17 Mar 2020 07:30:28 GMT

Hi Mackie

COUNT() function should not return BLANK(), it should be a digit like 0

So, try

count_product_key = IF ( CALCULATE ( COUNT('table'[product_key]) ) = 0, BLANK(), CALCULATE ( COUNT('table'[product_key]) ) )

Re: Set blank and set zero properly when drawing line chart

Mackie — Tue, 17 Mar 2020 08:17:47 GMT

Hi, az38 .

I tried your measure but it does not report zero properly.

Could you download this Power BI file and open it? I created two charts using my measure and your measure.

https://sharpsynappx-my.sharepoint.com/:u:/g/personal/mackie_sharpsynappx_onmicrosoft_com/EW_5zztrkypMnKJ9dn0s-nsB5GIS6ZG7pIU_XRSytqx11Q?e=VxBOqr

And the problem I want to solve is shown in this picture. That is to remove the unccesary part.

Re: Set blank and set zero properly when drawing line chart

az38 — Tue, 17 Mar 2020 08:27:49 GMT

Mackie

itsnot a DAX question, I think

you could set filter visual count_product_key2 is greater then 0, for example

I m not sure how exactly do you want to see a result, for example for point 2019 December and product_key=1