Distinct count not working for multiple filter selection

[sadhnabatra]

I am trying to replicate the first table using a measure in the second table. Unfortunately the distinct count is not broken down as it is in the first table.

In the first table I have applied all the fiter in the visual but in the second table I have used a measure to apply those measure. I am not sure what I am doing wrong.

Any help is immensely appreciated.

Thanks!

[v-jiascu-msft]

Hi @sadhnabatra,

The wildcard wouldn't work here. You can try this formula.

count =
CALCULATE (
    DISTINCTCOUNT ( MarketPenetrationView[SiteID] ),
    FILTER (
        MarketPenetrationView,
        SEARCH ( "HQ", [SiteCode], 1, 0 ) = 0
            && NOT ISBLANK ( MarketPenetrationView[BIReportCategory] )
            && ISBLANK ( MarketPenetrationView[ProductStatus] )
    )
)

If this doesn't work, please provide a sample in text mode. (pbix file would be great. )

Best Regards!

Dale

[sadhnabatra]

Thank you @v-jiascu-msft Dale! This seems to work but somehow the performance of the visual has drastically downgraded.

Do you have any suggestion to improve it? Could it be because of the seach function in the measure?

[v-jiascu-msft]

Hi @sadhnabatra,

There are too many conditions in the FILTER and also the nested Search function. We can try to add a new calculated column of SEARCH in the table. The computation would be downgrade.

ifHQ =
SEARCH ( "HQ", [SiteCode], 1, 0 ) 

count =
CALCULATE (
    DISTINCTCOUNT ( MarketPenetrationView[SiteID] ),
    FILTER (
        MarketPenetrationView,
        MarketPenetrationView[ifHQ] = 0
            && NOT ISBLANK ( MarketPenetrationView[BIReportCategory] )
            && ISBLANK ( MarketPenetrationView[ProductStatus] )
    )
)

Best Regards!

Dale