Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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

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

 

 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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

8 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , Try like

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

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

      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper User
        5 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago

        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