Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

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

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 : 

relationship.png

line and stacked column chart set up:

 

setup.png

 

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

 

test scenario 1.png

 

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:

simple result.png

 

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:
 
silly result.png
 
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!
4 REPLIES 4
Anonymous
Not applicable

test variable

 

DateCustomerDEF
1/1/2018A580
1/2/2018A535
1/3/2018A204
1/4/2018A860
1/5/2018A911
1/6/2018A561
1/7/2018A006
1/8/2018A1310
1/9/2018A051
1/10/2018A1022
1/11/2018A202
1/12/2018A050
1/13/2018A150
1/14/2018A980
1/15/2018A800
1/16/2018A8410
1/17/2018A901
1/18/2018A860
1/19/2018A409
1/20/2018A493
1/21/2018A1070
1/22/2018A926
1/23/2018A588
1/24/2018A818
1/25/2018A7310
1/26/2018A2102
1/27/2018A1062
1/28/2018A909
1/29/2018A270
1/30/2018A1090
1/31/2018A430
2/1/2018A550
2/2/2018A123
2/3/2018A042
2/4/2018A256
2/5/2018A454
2/6/2018A600
2/7/2018A017
2/8/2018A007
2/9/2018A300
2/10/2018A880
2/11/2018A067
2/12/2018A619
2/13/2018A006
2/14/2018A004
2/15/2018A009
2/16/2018A0310
2/17/2018A1000
2/18/2018A227
2/19/2018A800
2/20/2018A972
2/21/2018A1079
2/22/2018A136
2/23/2018A001
2/24/2018A500
2/25/2018A904
2/26/2018A569
2/27/2018A917
2/28/2018A589
3/1/2018A182
3/2/2018A640
3/3/2018A090
3/4/2018A130
3/5/2018A301
3/6/2018A709
3/7/2018A035
3/8/2018A281
3/9/2018A1020
3/10/2018A004
3/11/2018A102
3/13/2018A1038
3/14/2018A537
3/15/2018A599
3/16/2018A107
3/17/2018A063
3/18/2018A003
3/19/2018A560
3/20/2018A008
3/21/2018A625
3/22/2018A172
3/23/2018A813
3/24/2018A890
3/25/2018A088
3/26/2018A837
3/27/2018A789
3/28/2018A206
3/29/2018A320
3/30/2018A100
3/31/2018A036
4/1/2018A5010
4/2/2018A046
4/3/2018A4410
4/4/2018A612
4/5/2018A407
4/6/2018A0310
4/7/2018A940
4/9/2018A887
4/10/2018A003
4/11/2018A330
4/12/2018A0105
4/13/2018A071
4/14/2018A785
4/15/2018A300
4/16/2018A007
4/17/2018A331
4/18/2018A069
4/19/2018A487
4/20/2018A097
4/21/2018A202
4/22/2018A020
4/23/2018A048
4/24/2018A500
4/25/2018A430
4/26/2018A011
4/27/2018A561
4/28/2018A047
4/29/2018A009
4/30/2018A069

 

 

Anonymous
Not applicable

test customer

 

Customer
A
B
C
Anonymous
Not applicable

test sales

 

DateCustomerSales
1/1/2018A1241
1/1/2018B6852
1/1/2018C7955
1/2/2018B3277
1/2/2018A5277
1/3/2018A4669
1/3/2018C5103
1/3/2018B9911
1/4/2018A1516
1/4/2018B8829
1/4/2018C9871
1/5/2018A7849
1/6/2018C1434
1/6/2018B8936
1/7/2018A4768
1/7/2018C4815
1/7/2018B4927
1/8/2018B141
1/8/2018A786
1/8/2018C7204
1/9/2018C2666
1/9/2018B3459
1/10/2018C6078
1/10/2018A6887
1/10/2018B7233
1/12/2018C131
1/12/2018A4066
1/12/2018B7864
1/13/2018A164
1/14/2018A3747
1/15/2018A1466
1/15/2018C4640
1/16/2018C662
1/16/2018A929
1/17/2018B8443
1/17/2018A8821
1/18/2018C7678
1/18/2018B8076
1/19/2018C1178
1/19/2018B7280
1/20/2018A3964
1/21/2018C5561
1/21/2018A6353
1/21/2018B8539
1/22/2018A4098
1/22/2018C9589
1/23/2018A9946
1/24/2018B964
1/24/2018C5702
1/25/2018B6908
1/25/2018C8270
1/26/2018C3860
1/26/2018A9695
1/27/2018A4811
1/27/2018B8286
1/28/2018B1363
1/28/2018A5177
1/29/2018B137
1/29/2018A6458
1/30/2018B6446
1/30/2018A7013
1/30/2018C7070
1/31/2018C3954
1/31/2018B4033
1/31/2018A4975
2/2/2018A571
2/2/2018B8182
2/3/2018B6470
2/3/2018A6974
2/3/2018C8027
2/4/2018C1726
2/4/2018B4215
2/4/2018A4893
2/5/2018A1264
2/5/2018B4302
2/6/2018B1980
2/6/2018C7301
2/7/2018C6923
2/8/2018A104
2/8/2018C2994
2/9/2018B1704
2/9/2018C5691
2/10/2018C521
2/10/2018B1185
2/11/2018B4760
2/11/2018A4967
2/11/2018C7563
2/12/2018A7938
2/12/2018B7977
2/13/2018B2526
2/13/2018A9394
2/14/2018B4608
2/14/2018A5105
2/15/2018C6757
2/16/2018B2188
2/16/2018C9907
2/17/2018C4569
2/17/2018A5065
2/17/2018B7435
2/18/2018A2774
2/18/2018B3769
2/18/2018C4101
2/19/2018B5348
2/19/2018A5463
2/19/2018C6381
2/20/2018A3123
2/20/2018B7035
2/22/2018B105
2/22/2018A5256
2/22/2018C8966
2/23/2018A194
2/23/2018B5252
2/24/2018B6975
2/25/2018C564
2/25/2018B4588
2/25/2018A9726
2/26/2018A716
2/26/2018B912
2/27/2018C4625
2/27/2018A7199
2/28/2018A2069
2/28/2018B8746
2/28/2018C9359
3/1/2018C1499
3/1/2018A3945
3/1/2018B8818
3/2/2018A540
3/2/2018C6243
3/3/2018C3927
3/3/2018B4409
3/4/2018B2890
3/4/2018A3944
3/5/2018B4442
3/5/2018A6715
3/5/2018C6888
3/6/2018A2518
3/6/2018B4293
3/6/2018C7544
3/7/2018B125
3/7/2018C2056
3/8/2018C6126
3/9/2018B3600
3/9/2018C8036
3/10/2018B805
3/10/2018A2382
3/10/2018C7504
3/11/2018A3178
3/11/2018C7098
3/12/2018C8028
3/13/2018A1506
3/13/2018B3576
3/14/2018B2062
3/14/2018A7249
3/15/2018B4011
3/15/2018C6601
3/16/2018A120
3/16/2018C3941
3/17/2018B2066
3/18/2018C3279
3/18/2018A4141
3/18/2018B4577
3/19/2018B7929
3/20/2018A2460
3/20/2018B6071
3/21/2018C1012
3/21/2018A5058
3/21/2018B7118
3/22/2018C2975
3/22/2018A8648
3/23/2018A1351
3/23/2018B4064
3/24/2018B1648
3/24/2018A2192
3/24/2018C6505
3/25/2018B1371
3/25/2018C7664
3/25/2018A8755
3/26/2018A1544
3/26/2018C3101
3/26/2018B3807
3/27/2018B2101
3/27/2018A9974
3/28/2018B5427
3/28/2018A6568
3/28/2018C8867
3/29/2018B2460
3/29/2018C4447
3/30/2018A1674
3/30/2018C1942
3/31/2018A6279
3/31/2018B8016
3/31/2018C8386
4/1/2018B3319
4/1/2018A4926
4/1/2018C8488
4/2/2018C800
4/2/2018B3219
4/3/2018A5369
4/4/2018A9929
4/5/2018B2051
4/5/2018A2374
4/5/2018C6077
4/6/2018A8801
4/7/2018A7657
4/7/2018C7957
4/8/2018C3922
4/9/2018B2414
4/9/2018A8727
4/10/2018C2698
4/10/2018A5787
4/10/2018B7375
4/11/2018C1500
4/11/2018A4502
4/11/2018B6191
4/12/2018B533
4/12/2018C4337
4/12/2018A4754
4/13/2018A7063
4/13/2018C9928
4/14/2018A728
4/14/2018C7193
4/14/2018B9849
4/15/2018A5514
4/15/2018B9199
4/16/2018C3427
4/16/2018A8524
4/17/2018C822
4/17/2018B1278
4/17/2018A5204
4/18/2018B1727
4/18/2018A2915
4/19/2018B6435
4/19/2018A6630
4/20/2018C6734
4/21/2018C124
4/21/2018B9759
4/22/2018A525
4/22/2018C6214
4/22/2018B7602
4/23/2018B2355
4/23/2018C2542
4/23/2018A9880
4/24/2018B3430
4/24/2018A7096
4/24/2018C7700
4/25/2018A2976
4/25/2018B9162
4/26/2018A1869
4/26/2018B4363
4/26/2018C4412
4/28/2018B3214
4/28/2018A7237
4/28/2018C9366
4/29/2018B2543
4/29/2018C7065
4/30/2018B3494
4/30/2018A9340
Anonymous
Not applicable

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.