Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
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.
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.
@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.
User | Count |
---|---|
96 | |
85 | |
77 | |
66 | |
63 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
59 |