Skip to content
Advertisement

SELECT … WHERE Query in DAX

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%'

s1

The exact DAX equivalent Measure would be

Measure =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[doc] ),
    FILTER ( VALUES ( 'Table'[doc] ), LEFT ( 'Table'[doc], 2 ) = "ds" )
)

s2

Internally FILTER ( VALUES ( 'Table'[doc] ), LEFT ( 'Table'[doc], 2 ) = "ds" ) returns a table like this

s3

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement