Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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 = if(isblank(temp),0,temp) but only between the dates that was selected in the slicer.
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 |
test customer
Customer |
A |
B |
C |
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 |
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 |
User | Count |
---|---|
20 | |
18 | |
15 | |
11 | |
7 |
User | Count |
---|---|
28 | |
27 | |
13 | |
12 | |
12 |