Bar chart : Displaying all date on X-axis with zero count() for unselected rows

[Thathayoyo]

Hello,

I have two dataset (Dataset 1 and Dataset 2) link by a key (combination of columnA, columnB and cloumnC values).  On a sheet 1 which contains a Table of dataset 1, users can select rows they want to display (see for exemple rows in red in Dataset 1).  On a sheet 2 I have a bar chart where i display on X-axis the column Date ([Dataset2.columnDate.autoCalendar.Date]) and on Y-axis the Count([Dataset2.columnA]).

The issue is that because my two datasets are linked by a key, when i select rows in Dataset 1, then on my bart char i will only see on Y-axis Date from the row seleted with the same key in the Dataset 2 ( See exemple A).

I want on my bar chart that all possible Date on Y-axis to appear but with a count at 0 if they are unselected rows (See exemple B).

Thanks.

[seanbruton]

Please try this for a solution...

Counts can be like a filter at times. On your Dataset 2 table add in your script 1 as ind. Then sum (ind). Conclusion sum aggregation brings back zeros. 

[Qrishna]

This is can be resolved in multiple ways. couple of the Most used methods can be:

1. Using Set Expression
2. Using Alternate States (this becomes a little complex when you have complex expressions in your chart)

Method1: using Set Expression
in the bar chart:

Dimension: =Aggr(Only({$<DataSet1.ColumnA=>}DataSet2.ColumnDate), DataSet2.ColumnDate)
here im excluding the selections from DataSet1.ColumnA.
You can exclude as many columns as you want, if the exclude columns list is handful you can keep inserting {$<DataSet1.ColumnA=,DataSet1.ColumnB=...>}.
If you think your exclude field list is too big, u can use ({$<[$(=Concat({1<$Field-={'include_ColumnZ',}>}distinct $Field,']=,[')&']=')>} ColumnDate),
this ignores all fields selections except include_ColumnZ field selections

Measure: count(DataSet2.ColumnA) - you can modify your count per your need here, to show with getselectedcoutn()and with other techniques. for count = 0 to show up on bar chart, i see the bars wont show up if you have count 0 as there wont be any length to the bars.

No Selections:

Selction from ColumnA and No Set Expression in Barchart:

With Selection from ColumnA and Set Expression in Bar Chart: As you can see theres no effect on bar chart from Dataset1.columnA Selections

Methd2: Alternate States:

Method2: Alternate States
- create 2 Alternates States
- Create Tab Chrt With Alernate States A and Bar chart in sheet2 with Alternate States B

[Kushal_Chawda · Accepted solution]

@Thathayoyo  Let's assume your measure in line chart is Sum(Value), you can add below to your measure

=Sum(Value)+sum({1}0)

Make sure that "Include zero values" option is checked in properties Add-ons

[Thathayoyo]

Thanks, to write Sum(Value) + sum({1}0) fixed my problem.

[Thathayoyo]

Thanks, to write Sum(Value) + sum({1}0) fixed my problem.