Forum Discussion

litifeta's avatar
litifeta
Advocate II
10 years ago

Help with a count function in DAX

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...
Need Help
litifeta's avatar
litifeta
Advocate II
10 years ago

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

Sean's avatar
Sean
Community Champion
10 years ago

litifeta try this measure

 

Number of Projects =
CALCULATE (
    DISTINCTCOUNT ( TableName[ProjectsColumn] ),
    FILTER ( TableName, TableName[ProjectsColumn] <> "" )
)
  • litifeta's avatar
    litifeta
    Advocate II
    10 years ago

    sorry. no that did not work. 

    • Baskar's avatar
      Baskar
      Resident Rockstar
      10 years ago

      Can u please the screen shot which u have

      • Anonymous's avatar
        Anonymous
        Not applicable
        10 years ago

        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.