Power Pivot - Distinct Count - Blanks

[Sekushio]

I am having some issues getting the formula right to Distinct Count a column without counting the blank.

Distinct Count of Tech 2:=DISTINCTCOUNT([Tech])
-works but counts the blanks as 1 as well (there will not always be a blank)

Distinct Count of Tech 3:=CALCULATE([Distinct Count of Tech 2], not(isBLANK([Tech])))
-Semantic Error: The value for 'Tech' cannot be determined. Either 'Tech' doesn't exist, or there is no current row for a column named 'Tech'.

Distinct Count of Tech 5:=CALCULATE(DISTINCTCOUNT([Tech]),FILTER(NOT(ISBLANK([Tech]))))
-Semantic Error: Too few arguments were passed to the FILTER function. The minimum argument count for the function is 2.

Example of Column

Tech
2161
2161
2158
1703
2159

2159
1703

2161

2159
1703
1722

The first would tell me there are 6 unique techs working when there are only 5, because its counting the blank.

I am hoping I am just making a silly mistake and this can be done, and that what I learn from this can be applied to counts in other columns when wanting to only count a specific item.

 

[Ozeroth]

Hi there,

Your second formula should work if you qualify [Tech] with its table name, e.g. MyTable[Tech].
Always best practice to do this and in this case it's necessary for [Tech] to be recognized as a column.

[COLOR=#333333]Distinct Count of Tech 3:=CALCULATE([Distinct Count of Tech 2], NOT(ISBLANK(MyTable[Tech]) ) )
[/COLOR]

 

[Sekushio]

Thank you, but curious if I can do it without table name, as they are sheets with no tables. Would I put in the sheet name?

 

[Sekushio]

Anyone know if this is doable w/o a table? The amount of information being pulled in is a bit much and would rather it just use the sheet.

 

[Ozeroth]

Can you clarify what do you mean by "without a table"?
From your original question, you have a table in your data model with the column Tech don't you?

 

[Sekushio]

I'm sorry if it looks like that, but I do not have a table on the page I am using the Power Pivot on. It is a whole sheet and the rows are over 3000 with about 50 columns from its source. I may be doing it all wrong, but that is how I have it at the moment, unless the sheet is considered the table. Sorry if there is some confusion, I'm just getting into this excel thing.

 

[Sekushio]

So my sheet slapped me in the face this morning and I realize now that its a table (just looks like a sheet to me) and that its name is range and was created that way when I had hit power pivot. Lessoned learned..... however now that I have corrected the formula, no longer gives an error, but still counts the blank. Hopefully before lunch I'll have figured out how to right the correct formula. Thanks for responding earlier, it made me continue to question what I am doing.