Show All Categories on X Axis

[ExcelMonke]

Hello, 
I am currently building two bar charts and I need some help with X-Axis formatting. 

The problem:

In Chart 1) I am looking at a sub-group's performance, their "time buckets" only go up to 60 minutes.

In Chart 2) I am looking at the parent group performance, their "time buckets" go up to 240 minutes.

The desired solution: 
I want the X axis for both charts to be the same, to go up to 240 minutes. This means that Chart 1) will show the time bucket on the x axis, even if the value is 0.

Example Data:

Visit	Group	Sub-Group	Time Bucket
1	A	Ab	10
2	A	Ab	10
3	A	Ab	20
4	A	Ab	30
5	A	Ab	60
6	A	Ac	10
7	A	Ac	15
8	A	Ac	20
9	A	Ac	20
10	A	Ac	20
11	A	Ac	60
12	A	Ac	60
13	A	Ac	240
14	A	Ac	240

Desired Result:

Notice how the X-Axis in Chart 1 - which charts sub-group Ab - still show buckets 15 and 240, even if the value is 0

Thank you!

[Anonymous]

Hi @ExcelMonke ,

Please follow these steps:

According to your description, I created the following metric for the subgroup "Ab", and by the last judgment, the count result of 0 can also be displayed in the chart.

Measure =

VAR _count = CALCULATE(COUNT('Table'[Time Bucket]),FILTER(ALL('Table'),'Table'[Sub-Group] = "Ab" && 'Table'[Time Bucket] = MAX('Table'[Time Bucket])))

RETURN

IF(_count = 0,0,_count)

The final result is shown below.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ExcelMonke]

Thanks for the reply! So i tried to simplify the dataset in the problem. In actuality, the "Group" column comes from a different table (LocationDimension), and is linked to the visit table through the SubGroup name. How would I update the measure accordingly? Also it seems like I can't put the measure in the Axis.

[Daniel29195]

@ExcelMonke 

im not sure. 

maybe you enable the show items with no data. 

if not, maybe sharing the file if possible so i can take a closer look . 

best regards,

[ExcelMonke]

Thanks, that is how I got it to work on Excel, but I have not been able to find a similar button/option on Power BI. Any help finding it is also appreciated 🙂 

[Daniel29195]

@ExcelMonke 

you just need to right click on the column name. ( or click on the arrow down ) 

hope this helps . 

[ExcelMonke]

Hmmm, unfortunately that was not *quite* what I needed. It does show additional categories, however not at the level of the main group. For example, bucket 240 is still missing. 

[Daniel29195]

@ExcelMonke 

if your calculations are returning 0 ,  then power bi will show the bucket. 

in order to know show the buckets where  that are equal to zero ,

you need in your dax change zero to blank () .

exa : 

measure= 

switch( true () , sum(tbl_name[col_name])= 0 , blank() , sum(tbl_name[col_name])

let me know if this helps .