I have a SQL query:
select DISTINCT [doc] from [table] where [doc] like 'DS%'
I want to rewrite it with DAX, but it isn’t working. Here is what I have now:
= CALCULATE ( DISTINCTCOUNT ( 'table'[doc] ), SEARCH("DS", DISTINCT('table'[doc]),1,0) > 0 )
How would I create a similar query with DAX?
Advertisement
Answer
The SQL predicate that is used here translates to starts with ds
.
Example
declare @t1 as table (doc varchar(max)) insert into @t1 select * from (values('ds1'),('ds2'),('nods'),('no ds'),('nothing')) t(a) select * from @t1 select distinct(doc) from @t1 where doc like 'ds%'
The exact DAX equivalent Measure would be
Measure = CALCULATE ( DISTINCTCOUNT ( 'Table'[doc] ), FILTER ( VALUES ( 'Table'[doc] ), LEFT ( 'Table'[doc], 2 ) = "ds" ) )
Internally FILTER ( VALUES ( 'Table'[doc] ), LEFT ( 'Table'[doc], 2 ) = "ds" )
returns a table like this