Forum Discussion

manideep547's avatar
manideep547
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 ')
Need Help
Mariusz's avatar
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's avatar
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