Forum Discussion
Dax - distinctcount as SQL query (no exists)
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 |
4 Replies
venalMemorable Member
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.
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.