topic Display 0 instead of blank for line and stacked column chart with slicer in DAX Commands and Tips

Display 0 instead of blank for line and stacked column chart with slicer

Anonymous — Thu, 11 Jun 2020 16:39:56 GMT

Hello!

I am currently having difficulties in replacing blank with 0 on the line/stacked column chart and have no idea what I did wrong.

So if you could please let me know what I got wrong on my DAX, it would be much appreicated!

For the test data, I have 4 tables:

test date = one column of all date from 1/1/2018-4/30/2018

test customer = one column of all customer (A,B,C)

test sales = Date (1/1/2018-4/30/2018), Customer (A,B,C), Sales columns. Dates where no sales were made to any of the customers was not included

(ex: if no Sales were made to any of the customers on 1/1/2018, the data will start on 1/2/2018)

test variable = Date (1/1/2018-4/30/2018), Customer, D, E, F. Just like the test sales table, If all D,E,F column=0 on one day, the date was not included.

Relationship :

line and stacked column chart set up:

Let our testing scenario is as follows: (Date slicer is from 'test date' table, Customer Slicer from 'test customer' table)

Please notice that 3/12/2018 is completely missing.

Now, I would like to have the sales be equal to 0 if the data is missing. (So, this will force 3/12/2018 to show on the chart)

So I created a measure: SumSales = if(isblank(temp),0,temp)

Then, I got this as a result:

This would be perfect if the date showed between 3/8/2018-3/14/2018.

However, the date goes all the way from 1/1/2018 to 12/31/2018, which is weird since date range 5/1/2018-12/31/2018 does not exist in any of the tables.

So I tried to changing the DAX a bit :

SumSales =

var mindate=min('test sales'[Date])

var maxdate=max('test sales'[Date])

var test1=if(and(max('test date'[Date])<=maxdate,min('test date'[Date])>=mindate),if(temp<>blank(),temp,0),blank())

return test1

Then, it got sillier:

So, the result I am looking for is :

SumSales = if(isblank(temp),0,temp) but only between the dates that was selected in the slicer.

If you could please let me know what I am doing wrong-- the set up, relationshipDAX set up-- it would be much appreciated!

Thank you!

Re: Display 0 instead of blank for line and stacked column chart with slicer

Anonymous — Thu, 11 Jun 2020 16:06:12 GMT

test date

Date

1/1/2018

1/2/2018

1/3/2018

1/4/2018

1/5/2018

1/6/2018

1/7/2018

1/8/2018

1/9/2018

1/10/2018

1/11/2018

1/12/2018

1/13/2018

1/14/2018

1/15/2018

1/16/2018

1/17/2018

1/18/2018

1/19/2018

1/20/2018

1/21/2018

1/22/2018

1/23/2018

1/24/2018

1/25/2018

1/26/2018

1/27/2018

1/28/2018

1/29/2018

1/30/2018

1/31/2018

2/1/2018

2/2/2018

2/3/2018

2/4/2018

2/5/2018

2/6/2018

2/7/2018

2/8/2018

2/9/2018

2/10/2018

2/11/2018

2/12/2018

2/13/2018

2/14/2018

2/15/2018

2/16/2018

2/17/2018

2/18/2018

2/19/2018

2/20/2018

2/21/2018

2/22/2018

2/23/2018

2/24/2018

2/25/2018

2/26/2018

2/27/2018

2/28/2018

3/1/2018

3/2/2018

3/3/2018

3/4/2018

3/5/2018

3/6/2018

3/7/2018

3/8/2018

3/9/2018

3/10/2018

3/11/2018

3/12/2018

3/13/2018

3/14/2018

3/15/2018

3/16/2018

3/17/2018

3/18/2018

3/19/2018

3/20/2018

3/21/2018

3/22/2018

3/23/2018

3/24/2018

3/25/2018

3/26/2018

3/27/2018

3/28/2018

3/29/2018

3/30/2018

3/31/2018

4/1/2018

4/2/2018

4/3/2018

4/4/2018

4/5/2018

4/6/2018

4/7/2018

4/8/2018

4/9/2018

4/10/2018

4/11/2018

4/12/2018

4/13/2018

4/14/2018

4/15/2018

4/16/2018

4/17/2018

4/18/2018

4/19/2018

4/20/2018

4/21/2018

4/22/2018

4/23/2018

4/24/2018

4/25/2018

4/26/2018

4/27/2018

4/28/2018

4/29/2018

4/30/2018

Re: Display 0 instead of blank for line and stacked column chart with slicer

Anonymous — Thu, 11 Jun 2020 16:08:37 GMT

test sales

Date	Customer	Sales
1/1/2018	A	1241
1/1/2018	B	6852
1/1/2018	C	7955
1/2/2018	B	3277
1/2/2018	A	5277
1/3/2018	A	4669
1/3/2018	C	5103
1/3/2018	B	9911
1/4/2018	A	1516
1/4/2018	B	8829
1/4/2018	C	9871
1/5/2018	A	7849
1/6/2018	C	1434
1/6/2018	B	8936
1/7/2018	A	4768
1/7/2018	C	4815
1/7/2018	B	4927
1/8/2018	B	141
1/8/2018	A	786
1/8/2018	C	7204
1/9/2018	C	2666
1/9/2018	B	3459
1/10/2018	C	6078
1/10/2018	A	6887
1/10/2018	B	7233
1/12/2018	C	131
1/12/2018	A	4066
1/12/2018	B	7864
1/13/2018	A	164
1/14/2018	A	3747
1/15/2018	A	1466
1/15/2018	C	4640
1/16/2018	C	662
1/16/2018	A	929
1/17/2018	B	8443
1/17/2018	A	8821
1/18/2018	C	7678
1/18/2018	B	8076
1/19/2018	C	1178
1/19/2018	B	7280
1/20/2018	A	3964
1/21/2018	C	5561
1/21/2018	A	6353
1/21/2018	B	8539
1/22/2018	A	4098
1/22/2018	C	9589
1/23/2018	A	9946
1/24/2018	B	964
1/24/2018	C	5702
1/25/2018	B	6908
1/25/2018	C	8270
1/26/2018	C	3860
1/26/2018	A	9695
1/27/2018	A	4811
1/27/2018	B	8286
1/28/2018	B	1363
1/28/2018	A	5177
1/29/2018	B	137
1/29/2018	A	6458
1/30/2018	B	6446
1/30/2018	A	7013
1/30/2018	C	7070
1/31/2018	C	3954
1/31/2018	B	4033
1/31/2018	A	4975
2/2/2018	A	571
2/2/2018	B	8182
2/3/2018	B	6470
2/3/2018	A	6974
2/3/2018	C	8027
2/4/2018	C	1726
2/4/2018	B	4215
2/4/2018	A	4893
2/5/2018	A	1264
2/5/2018	B	4302
2/6/2018	B	1980
2/6/2018	C	7301
2/7/2018	C	6923
2/8/2018	A	104
2/8/2018	C	2994
2/9/2018	B	1704
2/9/2018	C	5691
2/10/2018	C	521
2/10/2018	B	1185
2/11/2018	B	4760
2/11/2018	A	4967
2/11/2018	C	7563
2/12/2018	A	7938
2/12/2018	B	7977
2/13/2018	B	2526
2/13/2018	A	9394
2/14/2018	B	4608
2/14/2018	A	5105
2/15/2018	C	6757
2/16/2018	B	2188
2/16/2018	C	9907
2/17/2018	C	4569
2/17/2018	A	5065
2/17/2018	B	7435
2/18/2018	A	2774
2/18/2018	B	3769
2/18/2018	C	4101
2/19/2018	B	5348
2/19/2018	A	5463
2/19/2018	C	6381
2/20/2018	A	3123
2/20/2018	B	7035
2/22/2018	B	105
2/22/2018	A	5256
2/22/2018	C	8966
2/23/2018	A	194
2/23/2018	B	5252
2/24/2018	B	6975
2/25/2018	C	564
2/25/2018	B	4588
2/25/2018	A	9726
2/26/2018	A	716
2/26/2018	B	912
2/27/2018	C	4625
2/27/2018	A	7199
2/28/2018	A	2069
2/28/2018	B	8746
2/28/2018	C	9359
3/1/2018	C	1499
3/1/2018	A	3945
3/1/2018	B	8818
3/2/2018	A	540
3/2/2018	C	6243
3/3/2018	C	3927
3/3/2018	B	4409
3/4/2018	B	2890
3/4/2018	A	3944
3/5/2018	B	4442
3/5/2018	A	6715
3/5/2018	C	6888
3/6/2018	A	2518
3/6/2018	B	4293
3/6/2018	C	7544
3/7/2018	B	125
3/7/2018	C	2056
3/8/2018	C	6126
3/9/2018	B	3600
3/9/2018	C	8036
3/10/2018	B	805
3/10/2018	A	2382
3/10/2018	C	7504
3/11/2018	A	3178
3/11/2018	C	7098
3/12/2018	C	8028
3/13/2018	A	1506
3/13/2018	B	3576
3/14/2018	B	2062
3/14/2018	A	7249
3/15/2018	B	4011
3/15/2018	C	6601
3/16/2018	A	120
3/16/2018	C	3941
3/17/2018	B	2066
3/18/2018	C	3279
3/18/2018	A	4141
3/18/2018	B	4577
3/19/2018	B	7929
3/20/2018	A	2460
3/20/2018	B	6071
3/21/2018	C	1012
3/21/2018	A	5058
3/21/2018	B	7118
3/22/2018	C	2975
3/22/2018	A	8648
3/23/2018	A	1351
3/23/2018	B	4064
3/24/2018	B	1648
3/24/2018	A	2192
3/24/2018	C	6505
3/25/2018	B	1371
3/25/2018	C	7664
3/25/2018	A	8755
3/26/2018	A	1544
3/26/2018	C	3101
3/26/2018	B	3807
3/27/2018	B	2101
3/27/2018	A	9974
3/28/2018	B	5427
3/28/2018	A	6568
3/28/2018	C	8867
3/29/2018	B	2460
3/29/2018	C	4447
3/30/2018	A	1674
3/30/2018	C	1942
3/31/2018	A	6279
3/31/2018	B	8016
3/31/2018	C	8386
4/1/2018	B	3319
4/1/2018	A	4926
4/1/2018	C	8488
4/2/2018	C	800
4/2/2018	B	3219
4/3/2018	A	5369
4/4/2018	A	9929
4/5/2018	B	2051
4/5/2018	A	2374
4/5/2018	C	6077
4/6/2018	A	8801
4/7/2018	A	7657
4/7/2018	C	7957
4/8/2018	C	3922
4/9/2018	B	2414
4/9/2018	A	8727
4/10/2018	C	2698
4/10/2018	A	5787
4/10/2018	B	7375
4/11/2018	C	1500
4/11/2018	A	4502
4/11/2018	B	6191
4/12/2018	B	533
4/12/2018	C	4337
4/12/2018	A	4754
4/13/2018	A	7063
4/13/2018	C	9928
4/14/2018	A	728
4/14/2018	C	7193
4/14/2018	B	9849
4/15/2018	A	5514
4/15/2018	B	9199
4/16/2018	C	3427
4/16/2018	A	8524
4/17/2018	C	822
4/17/2018	B	1278
4/17/2018	A	5204
4/18/2018	B	1727
4/18/2018	A	2915
4/19/2018	B	6435
4/19/2018	A	6630
4/20/2018	C	6734
4/21/2018	C	124
4/21/2018	B	9759
4/22/2018	A	525
4/22/2018	C	6214
4/22/2018	B	7602
4/23/2018	B	2355
4/23/2018	C	2542
4/23/2018	A	9880
4/24/2018	B	3430
4/24/2018	A	7096
4/24/2018	C	7700
4/25/2018	A	2976
4/25/2018	B	9162
4/26/2018	A	1869
4/26/2018	B	4363
4/26/2018	C	4412
4/28/2018	B	3214
4/28/2018	A	7237
4/28/2018	C	9366
4/29/2018	B	2543
4/29/2018	C	7065
4/30/2018	B	3494
4/30/2018	A	9340

Re: Display 0 instead of blank for line and stacked column chart with slicer

Anonymous — Thu, 11 Jun 2020 16:10:38 GMT

test customer

Customer

Re: Display 0 instead of blank for line and stacked column chart with slicer

Anonymous — Thu, 11 Jun 2020 16:12:22 GMT

test variable

Date	Customer	D	E	F
1/1/2018	A	5	8	0
1/2/2018	A	5	3	5
1/3/2018	A	2	0	4
1/4/2018	A	8	6	0
1/5/2018	A	9	1	1
1/6/2018	A	5	6	1
1/7/2018	A	0	0	6
1/8/2018	A	1	3	10
1/9/2018	A	0	5	1
1/10/2018	A	10	2	2
1/11/2018	A	2	0	2
1/12/2018	A	0	5	0
1/13/2018	A	1	5	0
1/14/2018	A	9	8	0
1/15/2018	A	8	0	0
1/16/2018	A	8	4	10
1/17/2018	A	9	0	1
1/18/2018	A	8	6	0
1/19/2018	A	4	0	9
1/20/2018	A	4	9	3
1/21/2018	A	10	7	0
1/22/2018	A	9	2	6
1/23/2018	A	5	8	8
1/24/2018	A	8	1	8
1/25/2018	A	7	3	10
1/26/2018	A	2	10	2
1/27/2018	A	10	6	2
1/28/2018	A	9	0	9
1/29/2018	A	2	7	0
1/30/2018	A	10	9	0
1/31/2018	A	4	3	0
2/1/2018	A	5	5	0
2/2/2018	A	1	2	3
2/3/2018	A	0	4	2
2/4/2018	A	2	5	6
2/5/2018	A	4	5	4
2/6/2018	A	6	0	0
2/7/2018	A	0	1	7
2/8/2018	A	0	0	7
2/9/2018	A	3	0	0
2/10/2018	A	8	8	0
2/11/2018	A	0	6	7
2/12/2018	A	6	1	9
2/13/2018	A	0	0	6
2/14/2018	A	0	0	4
2/15/2018	A	0	0	9
2/16/2018	A	0	3	10
2/17/2018	A	10	0	0
2/18/2018	A	2	2	7
2/19/2018	A	8	0	0
2/20/2018	A	9	7	2
2/21/2018	A	10	7	9
2/22/2018	A	1	3	6
2/23/2018	A	0	0	1
2/24/2018	A	5	0	0
2/25/2018	A	9	0	4
2/26/2018	A	5	6	9
2/27/2018	A	9	1	7
2/28/2018	A	5	8	9
3/1/2018	A	1	8	2
3/2/2018	A	6	4	0
3/3/2018	A	0	9	0
3/4/2018	A	1	3	0
3/5/2018	A	3	0	1
3/6/2018	A	7	0	9
3/7/2018	A	0	3	5
3/8/2018	A	2	8	1
3/9/2018	A	10	2	0
3/10/2018	A	0	0	4
3/11/2018	A	1	0	2
3/13/2018	A	10	3	8
3/14/2018	A	5	3	7
3/15/2018	A	5	9	9
3/16/2018	A	1	0	7
3/17/2018	A	0	6	3
3/18/2018	A	0	0	3
3/19/2018	A	5	6	0
3/20/2018	A	0	0	8
3/21/2018	A	6	2	5
3/22/2018	A	1	7	2
3/23/2018	A	8	1	3
3/24/2018	A	8	9	0
3/25/2018	A	0	8	8
3/26/2018	A	8	3	7
3/27/2018	A	7	8	9
3/28/2018	A	2	0	6
3/29/2018	A	3	2	0
3/30/2018	A	1	0	0
3/31/2018	A	0	3	6
4/1/2018	A	5	0	10
4/2/2018	A	0	4	6
4/3/2018	A	4	4	10
4/4/2018	A	6	1	2
4/5/2018	A	4	0	7
4/6/2018	A	0	3	10
4/7/2018	A	9	4	0
4/9/2018	A	8	8	7
4/10/2018	A	0	0	3
4/11/2018	A	3	3	0
4/12/2018	A	0	10	5
4/13/2018	A	0	7	1
4/14/2018	A	7	8	5
4/15/2018	A	3	0	0
4/16/2018	A	0	0	7
4/17/2018	A	3	3	1
4/18/2018	A	0	6	9
4/19/2018	A	4	8	7
4/20/2018	A	0	9	7
4/21/2018	A	2	0	2
4/22/2018	A	0	2	0
4/23/2018	A	0	4	8
4/24/2018	A	5	0	0
4/25/2018	A	4	3	0
4/26/2018	A	0	1	1
4/27/2018	A	5	6	1
4/28/2018	A	0	4	7
4/29/2018	A	0	0	9
4/30/2018	A	0	6	9