How to create Clustered Column Chart without counting Blanks

[goofydoggy]

Hi,

I have created a Column Clustered Chart to count the number of customers who have purchased the respective items from each Level of the supermart:

1. X-axis -> 'Level1' + 'Level2' + 'Level3' (for drill-down)

2. Y-axis -> COUNT of 'Customer'

Customer	Level1	Level2	Level3
A	Fruit	Board	Shirt
B	Meat	Knife
C	Vegetable	Knife	Shirt
D	Vegetable
E	Fruit	Cup	Pant

However, 'Blanks' appears on the X-axis with the count of 'Blanks' for Level2 and Level3. Thus, I created the following Measure to avoid counting for the 'Blanks', but it does not work:

SWITCH(

   COUNTAX(Table,Table[L1]),

  COUNTAX(Table,Table[L2]),

  COUNTAX(Table,Table[L3])

)

Kindly advise. Thank you!

[v-chenwuz-msft · Accepted solution]

Hi @goofydoggy ,

This:

Measure:

=
SWITCH (
    TRUE (),
    ISINSCOPE ( 'Table'[Level1] ), COUNTROWS ( FILTER ( 'Table', [Level1] <> BLANK () ) ),
    ISINSCOPE ( 'Table'[Level2] ), COUNTROWS ( FILTER ( 'Table', [Level2] <> BLANK () ) ),
    ISINSCOPE ( 'Table'[Level3] ), COUNTROWS ( FILTER ( 'Table', [Level3] <> BLANK () ) )
)

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

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

[v-chenwuz-msft · Accepted solution]

Hi @goofydoggy ,

This:

Measure:

=
SWITCH (
    TRUE (),
    ISINSCOPE ( 'Table'[Level1] ), COUNTROWS ( FILTER ( 'Table', [Level1] <> BLANK () ) ),
    ISINSCOPE ( 'Table'[Level2] ), COUNTROWS ( FILTER ( 'Table', [Level2] <> BLANK () ) ),
    ISINSCOPE ( 'Table'[Level3] ), COUNTROWS ( FILTER ( 'Table', [Level3] <> BLANK () ) )
)

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

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

[goofydoggy]

Hi v-chenwuz-msft,

Thank you for sharing the optimized code. It is very concise and works! 🙏🏻

Have a great week!

[VijayP]

@goofydoggy  Check this video! I am not getting blanks even after checking the hierarchy!
https://drive.google.com/file/d/1Qc8xz3e4xCqfViKKpMOe5WV5DgmceCYn/view?usp=sharing

[goofydoggy]

Hi,

I am afraid that the 'Expand all down one level in the hierarchy' button is not applicable to my requirements. It produces the congregation of the levels on the X-axis. I need to show only 1x Level on the X-axis at a time by drilling down with the 'Double arrow'.

[lbendlin]

I would strongly recommend against your approach as it will confuse your users (they expect different behavior from that visual).  But if you must, here is the required measure:

Count Cust = 
switch(TRUE()
,isinscope('Table'[Level3]) && not ISBLANK(max('Table'[Level3])),CALCULATE(DISTINCTCOUNT('Table'[Customer]),ALLEXCEPT('Table','Table'[Level3]))
,isinscope('Table'[Level2]) && not ISBLANK(max('Table'[Level2])),CALCULATE(DISTINCTCOUNT('Table'[Customer]),ALLEXCEPT('Table','Table'[Level2]))
,isinscope('Table'[Level1]) && not ISBLANK(max('Table'[Level1])),DISTINCTCOUNT('Table'[Customer]))

[goofydoggy · Accepted solution]

Hi lbendlin,

I made some minor modifications to your measure code and it worked! Yahoo! You are a Genius and Master!

Thank you very much for sharing your enlightening knowledge! 🙏🏻

Have a great week!

Count Cust =
switch(TRUE(),
isinscope('Table'[Level3]) && not ISBLANK(max('Table'[Level3])),COUNTAX('Table',IF('Table'[Level3]<>"",'Table'[Level3])),
isinscope('Table'[Level2]) && not ISBLANK(max('Table'[Level2])),COUNTAX('Table',IF('Table'[Level2]<>"",'Table'[Level2])),
isinscope('Table'[Level1]) && not ISBLANK(max('Table'[Level1])),COUNTAX('Table',IF('Table'[Level1]<>"",'Table'[Level1])))

[goofydoggy]

Hi lbendlin ,

Thank you for sharing the measure code swiftly. The users are expecting the visual to showcase 1x Level at a time on the chart.

Upon implementing it onto my dashboard, the 'Blanks' seem to be still present for Level2 and Level3.

Please advise. Thank you.

[VijayP]

@goofydoggy 

Check the file , I could not see any such blank count

[goofydoggy]

Hi,

Your chart is the combination of the 3x levels on the X-axis.

If you click on 'Drill Up' or 'Go to the next level of the hierarchy' arrows, you will be able to see the 'Blanks' on the charts.

From your PBIX:

- Level2

- Level3

[VijayP]

@goofydoggy 

When I tried with Simple implicit count , I am not getting any blanks!

It is better to show what result you are getting and what you want as a snapshot to give solution much better!

[goofydoggy]

Adding to my earlier message.

Referring to the Level2 and Level3 charts, I do not wish to have the 'Blanks' appearing on the X-axis.

Thank you.

[goofydoggy]

Hi,

I have setup my Clustered Column Chart as:

Here are the screenshots:

- Level1

There is no count of 'Blanks' as there is no blank data.

- Level2

The count of 'Blanks' appear.

- Level3

The count of 'Blanks' appear here too.

Thank you.