Help with a count function in DAX

[litifeta]

Having trouble with something that is so simple in Excel

I have a column of project names and blanks. I want a measure to calculate the unique text values so I can determine how many projects are in the list.

BLANK

Project A

Project A

Project C

Project A

Project B

Project A

In the above example the measure should return the value = 3. Thanks ahead of your response

[Baskar]

Its cool in Power BI too.

1. Look the visual levell filter, from there u can choose advance filter, and pick "Is not Blank"

2. Change our measure to "Distinct Count"

let me know if i wont work.

[litifeta]

Still do not know what you mean Baskar. do you mean = DISTINCTCOUNT(<DATA>)

[Baskar]

I will give u 2 suggestion

create measure with filter

1.  Measure = CALCULATE(DistinctCount(Project),FILTER('Table Name',"Project"<>BLANK())

it will filter your Blank()

2. Its based on Visual level filter in development canvas

Drag ur measure on filed and pick Distinct count 

Next move to Visual level filter, choose advanced filter, there u have to choose "Is not Blank()"

Then apply , i hope it will solve your prob

[Sean]

@litifeta try this measure

Number of Projects =
CALCULATE (
    DISTINCTCOUNT ( TableName[ProjectsColumn] ),
    FILTER ( TableName, TableName[ProjectsColumn] <> "" )
)

[litifeta]

sorry. no that did not work. 

[Baskar]

Can u please the screen shot which u have

[KHorseman]

It sounds like all you need is a plain DISTINCTCOUNT.

Project Count = DISTINCTCOUNT('Table Name'[Project])

@Seanit looks like DISTINCTCOUNT already ignores blanks, so that CALCULATE is probably redundant.

Edit: nope. I was wrong. Posted too fast. I did a quick test before posting this but I need to work on my reading comprehension. I forgot how many unique values were actually present in my test data, and I used a slightly different formula than @Sean. So I ended up using a bad formula to test a bad formula, and concluded that because they agreed they were both right. Turns out they were both wrong. *facepalm*

I made a test set with five rows, similar to the OP example. Three unique project names, one of them repeated, and one blank row. So the correct answer to the count is 3.

Measure A = DISTINCTCOUNT(TableName[Project])

yields a count of 4.  3 distinct values + 1 distinct blank, I guess.

Measure B = CALCULATE(DISTINCTCOUNT(TableName[Project], NOT(ISBLANK(TableName[Project])))

yields a count of 4. I thought ISBLANK([whatever]) was interchangeable with [whatever] = "" but it seems not. I have no idea why. Does a blank in a text column not evaluate true for ISBLANK? Maybe ISBLANK only works on numerical values? I don't see anything in he function documentation to suggest this.

Measure C = CALCULATE(DISTINCTCOUNT(TableName[Project]), TableName[Project] <> "")

(@Sean's suggestion) yields 3, the correct answer.

I've tested a little further and found that if the column in question is numerical (integer, decimal, date...) you cannot use the TableName[Column] <> "" construction. The formula simply fails. For those you have to use NOT(ISBLANK(TableName[Column])). I kind of get why <> "" wouldn't make sense for non-text columns, but I still don't get why NOT(ISBLANK()) fails on text.

/end edit.

Does that not give you the results you are looking for @litifeta? If not you're going to need to give us more information about any other requirements you have.

[Sean]

@KHorseman I wrote this last night without testing it first...

But just tested and it seems it doesn't ignore blanks

EDIT: Wow! @KHorseman How much coffee did you drink? I had not seen the Edit...

[KHorseman]

@Sean my company database has crapped out this morning so I have nothing to do. So it's either this or play games on my phone. And now we all know a little more about how DAX handles blanks.

Edit: also, so much coffee. I'm basically on an IV drip at this point.

[Sean]

@KHorseman 

[Sean]

@KHorseman With a slight adjustment my formula works on both numbers and text columns - and so does NOT (ISBLANK ...

Projects TEXT & NUMBERS = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Project] ),
    FILTER ( 'Table', 'Table'[Project] <> BLANK() )
)

Projects ALT TEXT & NUMBERs  = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Project] ),
    NOT ( ISBLANK('Table'[Project]) )
)

[litifeta]

just for clarification. it is a column in Power BI desktop