Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
manideep547
Helper III
Helper III

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 ')

8 REPLIES 8
amitchandak
Super User
Super User

Assume you have a date slicer from date table that is joined to your table/fact

 

Measure =

var _min=minx(Date,Date[Date])

var _max=maxx(Date,Date[Date])

 

Return

calculate(distinctcount(Table[field),Filter(All(Date), Date[Date]<=_max && Date[Date]>=_min))

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @

Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

Mariusz
Community Champion
Community Champion

Hi @manideep547 

 

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

 

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

 

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 

Correct me if I'm wrong 

I used INTERSECT Dax function with countRows

@Mariusz 



Hi @manideep547 

 

INTERSECT will work as well.

 

Best Regards,
Mariusz

Please feel free to connect with me.
LinkedIn

 

Thank you ...!!!
@Mariusz 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.