Forum Discussion

manideep547's avatar
manideep547
Icon for Helper III rankHelper 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
Icon for Community Champion rankCommunity 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
Icon for Helper III rankHelper III
6 years ago

Correct me if I'm wrong 

I used INTERSECT Dax function with countRows

Mariusz 



  • Mariusz's avatar
    Mariusz
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi manideep547 

     

    INTERSECT will work as well.

     

    Best Regards,
    Mariusz

    Please feel free to connect with me.
    LinkedIn