Forum Discussion
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 ')
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
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
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
Correct me if I'm wrong
I used INTERSECT Dax function with countRows
MariuszThank you ...!!!
Mariusz
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