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

Correct me if I'm wrong 

I used INTERSECT Dax function with countRows

Mariusz 



  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    Hi manideep547 

     

    INTERSECT will work as well.

     

    Best Regards,
    Mariusz

    Please feel free to connect with me.
    LinkedIn