Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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...
Need Help
Tips and Tricks
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous 
    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's avatar
      Anonymous
      Not applicable
      6 years ago
      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