Dax - distinctcount as SQL query (no exists)

[Anonymous]

I need a Dax that count the Names distinctly that has the Flag=1  but don´t has the Flag=0

In SQL I could do something like this:

Select count(distinct Name) Qtd
from Table1 A
where Flag = 1
and not exists (select 1 from Table1 B where A.Name = B.Name and A.Flag =0)

Qtd Names With flag 1 but not 0 = ?

Name	Flag
Joao	1
Joao	0
Andre	1
Mateus	0

The result espected is just Andre, beucause he is the only that has the flag 1 and hasn´t the 0

Name	Flag
Andre	1

[v-jayw-msft]

Hi @souzacaleb，

Here’s my sample data:

Please check following steps as below:

1. Create calculated columns:

    Column =

    var a = CALCULATE(COUNTROWS('table'),FILTER('table','table'[Name] = EARLIER('table'[Name])))

    var b = CALCULATE(COUNTROWS('table'),FILTER('table','table'[Name] = EARLIER('table'[Name])&&'table'[Flag] = 1))

    return

    a=b

2. Create measure:

    Measure = CALCULATE(DISTINCTCOUNT('table'[Name]),FILTER('table','table'[Column] = TRUE()))

3. Result would be shown as below:

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-jayw-msft 
Is there any way achieve the same result without create the columns step? Is it possible to do all in a measure?
I aprecciate for you help. 

[v-jayw-msft]

Hi @Anonymous ,

The following measure might be helpful for you:

Measure 2 = CALCULATE(DISTINCTCOUNT('table'[Name]),FILTER('table',MIN('table'[Flag])>0))

And the result would be shown as below:

Best Regards,

Jay

[venal]

@Anonymous 

small change on the sql query.

select distinct a.Name
from FlagTable a
where a.Flag=1
and not exists(select 1 from FlagTable b where b.Flag=0 and a.Name=b.Name)

we will expect someone need to help with DAX.