cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EalTim
Helper IV
Helper IV

SQL code to Dax expression

Hi All,

 

I need a requirement of implement below sql query in DAX. 

  declare @startdate as date
  declare @enddate as date
  set @startdate = '2021-10-18'
   set @enddate = '2021-11-15'

  select distinct count (Item_Number) from [dbo].[TblItem] as i
inner join [dbo].[Customs] as c  on i.Item_Number = c.Item_Number
  Where    ActiveDate <= @startdate and (EndDate > @enddate or EndDate is null)

However, the ActiveDate and EndDate are in 7 days interval. For example '2021-10-18' on Monday and the next date is '2021-10-25' the next Monday and soon.(7 days intervals). The startdate act as a minimum date and the EndDate is the maximum date from their respective columns and falls within 3 months 

Thanks in advance
 

 

 

 

1 ACCEPTED SOLUTION
Eyelyn9
Community Support
Community Support

Hi @EalTim ,

 

I could not successfullt execute the SQL code on my side since it has error:

Ambiguous column name 'Item_Number'.

 

In Power BI, you may try:

Measure = 
var _start=DATE(2021,10,18)
var _end=DATE(2021,11,15)
return
CALCULATE(DISTINCTCOUNT(TblItem[Item_Number]), FILTER('Customs',[ActiveDate ]<=_start  && [EndDate]>= _end || [EndDate]=BLANK()))

Eyelyn9_0-1642475345836.png

 

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Eyelyn9
Community Support
Community Support

Hi @EalTim ,

 

I could not successfullt execute the SQL code on my side since it has error:

Ambiguous column name 'Item_Number'.

 

In Power BI, you may try:

Measure = 
var _start=DATE(2021,10,18)
var _end=DATE(2021,11,15)
return
CALCULATE(DISTINCTCOUNT(TblItem[Item_Number]), FILTER('Customs',[ActiveDate ]<=_start  && [EndDate]>= _end || [EndDate]=BLANK()))

Eyelyn9_0-1642475345836.png

 

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

smpa01
Super User
Super User

@EalTim  

declare @startdate as date 
declare @enddate as date 
set
   @startdate = '2021-10-18' 
   set
      @enddate = '2021-11-15' 
      select distinct
         count (Item_Number) 
      from
         [dbo].[TblItem] as i 
         inner join
            [dbo].[Customs] as c 
            on i.Item_Number = c.Item_Number 
      Where
         ActiveDate <= @startdate 
         and 
         (
            EndDate > @enddate 
            or EndDate is null
         )

this SQL looks ambiguous to me because it is not clear whether the filtering applies in 

[dbo].[TblItem]

or

[dbo].[Customs]

 

You can achieve a basic inner join like this

basicMeasure = calculate(distinctcount(item[Item_Number]),Customs)

//if filtering item
m1 = calculate(DISTINCTCOUNT(Item[Item_Number]),Filter(Item, Item[ActiveDate]<=DAY(2021,10,18)||(Item[EndDate]>=DAY(2021,11,15)||Item[EndDate]=BLANK()),(Customs))

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


smpa01
Super User
Super User

@EalTim  are 

[Customs]

and

[TblItem]

related?

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


@smpa01 Yes. they are related or joined with item_Number from both tables.

The ActiveDate and EndDate are both from TblItem

 

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!