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