Forum Discussion

Helper III

6 years ago

sql to dax

select distinctcount(*) from table where Date<' date' andDate>' date 'and customers id in (select Customer Id from table where date<'date' and date>' date ')

Community Champion

6 years ago

Can you provide a data sample and snap of an expected outcome?

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

Helper III

6 years ago

select distinctcount(*) from table where Date<1/1/2018andDate>' 1/1/2018'and customers id in (select Customer Id from table where date<'1/1/2017' and date>' 1/12/2017')

if the customer has the transaction in between 1/1/2018 to 1/1/2019 and also having the transaction in between 1/1/2017 to 1/1/2017
Mariusz
amitchandak

Mariusz
Community Champion
6 years ago
Hi manideep547

Try this.

Measure = VAR __conditionOne = CALCULATETABLE( VALUES( 'Table'[Customer Id] ), ALL( 'Table'[Date] ), 'Table'[Date] > DATE( 2017, 1, 1 ), 'Table'[Date] < DATE( 2017, 12, 1 ) ) RETURN CALCULATE( DISTINCTCOUNT( 'Table'[Customer Id] ), TREATAS( __conditionOne, 'Table'[Customer Id] ), ALL( 'Table'[Date] ), 'Table'[Date] > DATE( 2018, 1, 1 ), 'Table'[Date] < DATE( 2019, 1, 1 ) )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn
- manideep547
  Helper III
  6 years ago
  Correct me if I'm wrong
  
  I used INTERSECT Dax function with countRows
  
  Mariusz
  - Mariusz
    Community Champion
    6 years ago
    Hi manideep547
    
    INTERSECT will work as well.
    
    Best Regards,
    Mariusz
    
    Please feel free to connect with me.
    LinkedIn
- manideep547
  Helper III
  6 years ago
  Include average=500 in the above query
  who has average transactions is 500
  
  select distinctcount(*) from table where Date<' date' andDate>' date 'and average(Amount)='500'and customers id in (select Customer Id from table where date<'date' and date>' date 'and average(Amount)='500')
  Mariusz