line and Clustered Column Chart - count distintic do not exclude "no data values"

[Anonymous]

hi team, i'm very new to pbi and at the moment I only use the visualization tools and not DAX

i'm totally getting crazy since i'm tring to show on a line and clustered column chart some values but i would like to avoid any value that in my database is = empty cell

this because when i try to put these values on a chart, using the count distinct value, at the end i also have that the graph counting the values that are empty (and this generates a +1 value and it's not correct) empty = 1 in his calculation

any suggestion?

thanks

[Anonymous]

Hi @Anonymous ,

I created a simple table as below to mockup your scenario:

ID	Name
K1	aa
K2	bb
K3
K4	cc
K5	dd
K6	ee
K7
K8	ff

You can create a measure by right-click the related table the Fields pane and select New measure from the menu that appears. Then input the below formula in input box just as below screenshot.

Count distinct without blank values = COUNTA('Table'[Name])-COUNTBLANK('Table'[Name])

or

Count distinct without blank values = CALCULATE(DISTINCTCOUNT('Table'[Name]),'Table'[Name]<>BLANK())

Best Regards

[Anonymous]

apologise yingyinr but i'm still facing difficulties

my table is like this

a       count of a

xx      1

yy      1

zz      1

          1

how can i make it count as 3 ando not 4?

[Anonymous · Accepted solution]

Hi @Anonymous ,

You can create a measure as below:

count of a = 
VAR _tab =
    SUMMARIZE (
        'Table',
        'Table'[a],
        "count of a", CALCULATE ( COUNT ( 'Table'[a] ), FILTER ( 'Table', 'Table'[a] <> BLANK () ) )
    )
RETURN
    SUMX ( _tab, [count of a] )

Best Regards

[amitchandak]

@Anonymous , Try like

calculate(distinctcount(Table[column]), filter(Table, not(isblank(Table[column]))))

[Anonymous]

calculate(distinctcount(Table[column]), filter(Table, not(isblank(Table[column]))))

where should i put it and how do i understand which is the table and the column name? thanks

[Anonymous]

where should i put it? apologize for my (Stupid) question

[amitchandak]

@Anonymous , I suggested a measure that will not count blank. Hope I got it right.

[Anonymous]

the system says that syntax is incorrect