SSAS tabular DISTINCTCOUNTNOBLANK equivalent

Question

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

@Olly it shows error in the measure no message. it shows error sign — JonWay, May 18 '22 at 13:42

score 1 · Accepted Answer · answered May 18 '22 at 16:11

1

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]))
)

answered May 18 '22 at 16:11

GregGalloway

11,355
3
16
47

SSAS tabular DISTINCTCOUNTNOBLANK equivalent

1 Answers1