如何删除空值? [英] How can remove the null values?

问题描述

如何删除bottomcount中的空值，即我只想查看具有实际销售数量的产品，我尝试使用非空和非空的方法，但没有成功.

How can I remove the null values in the bottomcount, ie. i only want to see the products with actually sold units, I have tried with nonempty and non empty without success.

with
member [Measures].[Amount Sold] as
    ([Measures].[Internet Sales Amount]),
format_string = "currency"

select {[Measures].[Amount Sold]}
on columns,
bottomcount(
order(

{[Product].[Product].Members},
([Measures].[Amount Sold]), bdesc),
5 )on rows

推荐答案

您可以过滤[Product].成员使用NOT ISEMPTY()排除所有空值，然后对过滤后的集合进行底计数.

You could filter [Product].Members using NOT ISEMPTY() to exclude all the empty values, and then bottomcount the filtered set.

with member [Measures].[Amount Sold] as
    ([Measures].[Internet Sales Amount]),
    format_string = "currency"
select 
    {[Measures].[Amount Sold]} on columns,
    order(
        bottomcount(
            filter({[Product].[Product].Members}, NOT ISEMPTY([Measures].[Amount Sold])),
            5,
            [Measures].[Amount Sold]),
        [Measures].[Amount Sold], bdesc) on rows
from [Adventure Works]

请注意，BottomCount()将执行升序，中断层次结构排序，因此如果要降序，则需要在其顶部执行Order().

Note that BottomCount() will do an ascending, break-hierarchies sort, so you need to do the Order() on top of it if you want descending order.

这篇关于如何删除空值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！