CODEWITHSUNDEEP
ssasmdx
abianari
abianari

Reputation: 353

IN SSAS how to remove null value in distinct count measures

I have column in fact table .the column in some row has 'Null' value.i have measure based on this column with aggregate function Set to DistinctCount

this measure count null value too. but i don't want to count null value what should i do?

Upvotes: 0

Views: 5845

Answers (2)

Jordan Forrest
Jordan Forrest

Reputation: 11

One popular solution that works is to count from a view of the table that filters out the nulls. This works, but I would bet that it requires another scan of the fact table.

Another solution is like fighting fire with fire.
Add a computed column that is 0 if it's null and 1 if it's not: 

CASE WHEN _DollarsLY IS NULL THEN 0 ELSE 1 END AS _DistinctCountHackLY

Then you can do something like this in a cube calculation: 

iif(_DistinctCountHackLY=2 or _DollarsLY=null,_DistinctUPCLY-1,_DistinctUPCLY)

Upvotes: 1

Jeroen Bolle
Jeroen Bolle

Reputation: 1836

Most efficient would be to filter out NULL values in the data source view (using a named query for example). This won't affect performance too much as a distinct count measure is calculated in a separate measure group anyway.

Upvotes: 5

Related Questions