Forum Discussion

goofydoggy's avatar
goofydoggy
Frequent Visitor
3 years ago
Solved

How to create Clustered Column Chart without counting Blanks

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'...
Need Help
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    3 years ago

    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's avatar
    goofydoggy
    3 years ago

    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])))
lbendlin's avatar
lbendlin
Super User
3 years ago

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's avatar
goofydoggy
Frequent Visitor
3 years ago

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])))