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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
venkb
Helper I
Helper I

How to convert a SQL IN statement to DAX query

Hello, I am trying to convert the below SQL query to DAX but with no luck - Any help would be appreciated

 

Select * from A
where convert(date, DateTime) in ('2020-11-05', '2020-11-06')
and State = 'Step1'
and Status = 'Success'
and FileName in (Select FileName from A where convert(date, DateTime) = '2020-11-05' and State = 'Step2' and Status = 'Success')

 

A follow up question - The date is a user selected field that is provided through a slicer. In this case the user selected date is 2020-11-05 & i would want the DAX to include both user selected date & user selected date + 1 (As shown above ie 2020-11-05 & 2020-11-06). How can that be achieved?

2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@venkb , Try 

measure =
var _tab = summarize(filter(A, [date]= date(2020,11,05) && [State] ="Step2" && [Status] = "Status" ), A[FileName])
return
calculate(countrows(A) , filter(A , [FileName] in _tab && [State] ="Step2" && [Status] = "Status" ))

 

Or a table as 

Table =
var _tab = summarize(filter(A, [date]= date(2020,11,05) && [State] ="Step2" && [Status] = "Status" ), A[FileName])
return
calculatetable(A, filter(A , [FileName] in _tab && [State] ="Step2" && [Status] = "Status" ))

 

 

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

Icey
Community Support
Community Support

Hi @venkb ,

 

Since you want to pass slicer value into your DAX expression, it is needed to create a measure like below and put it into visual filter field.

Measure =
VAR FileName =
    SUMMARIZE (
        FILTER (
            ALLSELECTED ( A ),
            A[Date] = SELECTEDVALUE ( SlicerTable[Date] )
                && A[State] = "Step2"
                && A[Status] = "Success"
        ),
        [FileName]
    )
VAR Result =
    IF (
         (
            MAX ( A[Date] ) = SELECTEDVALUE ( SlicerTable[Date] )
                || MAX ( A[Date] )
                    = SELECTEDVALUE ( SlicerTable[Date] ) + 1
        )
            && MAX ( A[State] ) = "Step1"
            && MAX ( A[Status] ) = "Success"
            && MAX ( A[FileName] ) IN FileName,
        1
    )
RETURN
    Result

 A.JPG

 

 

Best regards

Icey

 

If this post helps, then consider Accepting it as the solution to help other members find it faster.

View solution in original post

5 REPLIES 5
venkb
Helper I
Helper I

Icey, i was able to get the count. thanks for your solution

venkb
Helper I
Helper I

thanks Icey. btw, how do i get the count as opposed to a table? so per the example that you have in your response i want to get a value of 2

Icey
Community Support
Community Support

Hi @venkb ,

 

Since you want to pass slicer value into your DAX expression, it is needed to create a measure like below and put it into visual filter field.

Measure =
VAR FileName =
    SUMMARIZE (
        FILTER (
            ALLSELECTED ( A ),
            A[Date] = SELECTEDVALUE ( SlicerTable[Date] )
                && A[State] = "Step2"
                && A[Status] = "Success"
        ),
        [FileName]
    )
VAR Result =
    IF (
         (
            MAX ( A[Date] ) = SELECTEDVALUE ( SlicerTable[Date] )
                || MAX ( A[Date] )
                    = SELECTEDVALUE ( SlicerTable[Date] ) + 1
        )
            && MAX ( A[State] ) = "Step1"
            && MAX ( A[Status] ) = "Success"
            && MAX ( A[FileName] ) IN FileName,
        1
    )
RETURN
    Result

 A.JPG

 

 

Best regards

Icey

 

If this post helps, then consider Accepting it as the solution to help other members find it faster.

venkb
Helper I
Helper I

thanks Amit. let me give it a try. in this example you have hard coded the 5th november date. what if i have to use the date the user selected in the slicer and i have use that and a day after the selected date?

amitchandak
Super User
Super User

@venkb , Try 

measure =
var _tab = summarize(filter(A, [date]= date(2020,11,05) && [State] ="Step2" && [Status] = "Status" ), A[FileName])
return
calculate(countrows(A) , filter(A , [FileName] in _tab && [State] ="Step2" && [Status] = "Status" ))

 

Or a table as 

Table =
var _tab = summarize(filter(A, [date]= date(2020,11,05) && [State] ="Step2" && [Status] = "Status" ), A[FileName])
return
calculatetable(A, filter(A , [FileName] in _tab && [State] ="Step2" && [Status] = "Status" ))

 

 

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.