Search code examples
visual-studiodaxssasssas-tabular

SSAS tabular DISTINCTCOUNTNOBLANK equivalent

I have the data below in SSAS tabular import from sql server

create table #Data (ID int,Names varchar(50))
insert into #Data values
(1,'one'),(2,NULL),(3,'two'),(4,'one'),
(5,NULL),(6,NULL),(7,'two'),(8,'three'),
(9,'one'),(10,NULL),(11,'two'),(12,'one'),
(13,NULL),(14,NULL),(15,'two'),(16,'three')
 select * from #Data
 drop table #Data

I want to count distinct value as a measure excluding blank from Names columns I wrote the below DAX it is given error

Total = CALCULATE(
                    DISTINCTCOUNTNOBLANK(Data[Names]),
                  filter(data,Data[ID]>10 
                   )
                   )

How can I write the DAX in SSAS Tabular in Visual studio

Thanks

Solution

  • Try this measure. The NOT(ISBLANK()) part is the is the equivalent of DISTINCTCOUNTNOBLANK.

    Total := CALCULATE(
 DISTINCTCOUNT(Data[Names]),
 Data[ID]>10,
 NOT(ISBLANK(Data[Names]))
)