DAX to filter using "IN" with a variable

[tomperro]

I am trying to use a DAX formula to filter and count rows using "IN" instead of EQUALS.
The formula below works for one specific cvalue, however, I would like to use a comma separted list.

'ESR'[Test_Number_esr]  = "1"

VAR __CurrentTestNumber =   'ESR'[Test_Number_esr]  

RETURN

    CALCULATE (

        COUNTROWS ( 'MyTests' ),

        FILTER (

            'MyTests',

            MyTests[TestNumber__m] = __CurrentTestNumber

        )

    )

)

I need to do something like this...

'ESR'[Test_Number_esr]  "1","2","3","4","5","6","7")

VAR __CurrentTestNumbers =   'ESR'[Test_Number_esr]  

RETURN

    CALCULATE (

        COUNTROWS ( 'MyTests' ),

        FILTER (

            'MyTests',

            MyTests[TestNumber__m] IN __CurrentTestNumbers

        )

    )

)

[Ashish_Mathur]

Hi,

Write this measure

=CALCULATE(COUNTROWS('MyTests'),FILTER(VALUES(MyTests[TestNumber__m]),MyTests[TestNumber__m] = 1||MyTests[TestNumber__m] = 2,||MyTests[TestNumber__m] = 3))

[Anonymous]

Hi @tomperro ,

Please try:

VAR __CurrentTestNumbers = { "1", "2", "3", "4", "5", "6", "7" }  
RETURN
    CALCULATE (
        COUNTROWS ( 'MyTests' ),
        FILTER (
            'MyTests',
            'MyTests'[TestNumber__m] IN __CurrentTestNumbers
        )
    )

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[tomperro]

@Anonymous  thank you for that response.
This works if I hard code the values VAR __CurrentTestNumbers = { "1", "2", "3", "4", "5", "6", "7" } 

I actually have a column that I am assigning the values to another column
Test Numbers To Check =
    VAR __CurrentTestNumber = unichar(34) & SUBSTITUTE('Employee Specific  Requirement'[Test_Number_s__c],";",unichar(34) & "," & unichar(34)) & unichar(34)

I need to use 'Test Numbers To Check' in my calculate formula, but this is not working.

VAR __CurrentTestNumbers = { Test Numbers To Check }
RETURN
CALCULATE (
COUNTROWS ( 'MyTests' ),
FILTER (
'MyTests',
'MyTests'[TestNumber__m] IN __CurrentTestNumbers
)
)

[Dangar332]

hi, @tomperro 

try below

answer = 

var maxofEmployee Specific  Requiremen = max('Employee Specific  Requirement'[Test_Number_s__c])

var minofEmployee Specific  Requiremen = min('Employee Specific  Requirement'[Test_Number_s__c])

var  __CurrentTestNumbers =

 GENERATESERIES(minofEmployee Specific  Requiremen , maxofEmployee Specific  Requiremen ,1)

var result = CALCULATE (

        COUNTROWS ( 'MyTests' ),

        FILTER (

            'MyTests',

            MyTests[TestNumber__m] IN __CurrentTestNumbers

                    )

               )

return  result 

Did i answer your question? Mark my post as a solution which help other people to find  fast and easily.

[tomperro]

@Dangar332 
I am receiving an error on the line MyTests[TestNumber__m] IN __CurrentTestNumbers stating __CurrentTestNumbers is not a valid table and also another error the function expects a table expression for argument '', but a string or numeric expression was used.

[Dangar332]

try below

answer = 

var maxofEmployee Specific  Requiremen = max('Employee Specific  Requirement'[Test_Number_s__c])

var minofEmployee Specific  Requiremen = min('Employee Specific  Requirement'[Test_Number_s__c])

var  __CurrentTestNumbers =

 calculatetable(GENERATESERIES(minofEmployee Specific  Requiremen , maxofEmployee Specific  Requiremen ,1))   ----------  write inside calculatetable

var result = CALCULATE (

        COUNTROWS ( 'MyTests' ),

        FILTER (

            'MyTests',

            MyTests[TestNumber__m] IN __CurrentTestNumbers

                    )

               )

return  result 

[tomperro]

@Dangar332  it looks like some of the test number inclue text.  So I can have a test numer "1i"
The arguments in GENERATESERIES function must be numeric or date/time type.
I can not change my data type because I need the text in the test number (1i)

[Dangar332]

hi @tomperro 

if your test number (1i) column like these then you have to seprate that value as mention below

use code to separate             

 LEFT('length'[Column1],LEN([Column1])-1)

 

it just example i am mention it in var of min and max

answer = 

var maxofEmployee Specific  Requiremen =values(left(max('Employee Specific  Requirement'[Test_Number_s__c]),len(Employee Specific  Requirement'[Test_Number_s__c])-1))

var minofEmployee Specific  Requiremen = values(left(min('Employee Specific  Requirement'[Test_Number_s__c]),len(Employee Specific  Requirement'[Test_Number_s__c])-1))

var  __CurrentTestNumbers =

 calculatetable(GENERATESERIES(minofEmployee Specific  Requiremen , maxofEmployee Specific  Requiremen ,1))   ----------  write inside calculatetable

var result = CALCULATE (

        COUNTROWS ( 'MyTests' ),

        FILTER (

            'MyTests',

            MyTests[TestNumber__m] IN __CurrentTestNumbers

                    )

               )

return  result 

if it is still not working then try to separate value of   test number (1i) column   after sepration use that column value as min and max as discuss above 

[rsbin]

@tomperro ,

This link should assist you with the correct syntax:

https://www.sqlbi.com/articles/the-in-operator-in-dax/

Regards,

[tomperro]

I do not see where I can use a variable.