How to convert a SQL IN statement to DAX query

[venkb]

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?

[amitchandak · Accepted solution]

@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.

[venkb]

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

[venkb]

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 · Accepted solution]

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

 

Best regards

Icey

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

[venkb]

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 · Accepted solution]

@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.