Equivalent of TSQL's IF EXISTS in DAX

I am looking for DAX's equivalent for TSQL's IF EXISTS.

Here is my table:

Name Flag
Emily 5
Victoria 5
Kimberly 5
John 4
Joe 5
Sam 5
Emily 4
Victoria 4
Sam 4
Emily 4
Victoria 5

and here is my TSQL query:
select count (distinct (name))
from table1 t1
where
  flag = 5
  and exists (select * from table1 t2 where t1.name=t2.name and flag=4)

It should return 3 (Emily, Victoria and Sam).

Basically, I am looking for count distinct name for rows that have flag=5 and at least have one flag=4 for the same person. Maybe it can also be described as union between two tables table1 for flag=5 and table2 for flag=4.

Can we do this in DAX?

Thanks

 
July 2nd, 2014 10:35am

You might write this measure:

Names :=
CALCULATE (
    COUNTA ( TABLE[name] ),
    CALCULATETABLE ( VALUES ( TABLE[name] )TABLE[flag] = 4 ),
    TABLE[flag] = 5
)

Free Windows Admin Tool Kit Click here and download it now
July 3rd, 2014 2:33am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics