CODEWITHSUNDEEP
reporting-servicesdax
bjornasm
bjornasm

Reputation: 2318

List as input parameter in DAX, ssrs

In an other Stack Overflow question I got the following code, to extract the right dataset depending on the input parameters in an SSRS report, using a DAX query. 

    EVALUATE
(
    CALCULATETABLE
    (
        ADDCOLUMNS (  
            'Case',
            "Casenumber", RELATED( 'Casedetails'[Casenumber]),
            "Casetitle", RELATED('Casedetails'[Casetitle]),
            "Date", RELATED ( 'Casedates'[Shortdate]),
            "Caselink", RELATED ( 'Casedetails'[Caselink]),
            "Place", RELATED('Geography'[Place])
            ),
//insert filters here
        'Casedates'[Shortdate] = @Date,
        'Geography'[Place] = @Place
    )
)

However, the user should and could be able to select all places (@Place). Doing this with the current code gives the following error:

Cannot convert value '{20030, 20377, 20361..' of type Text to type Number.

So, the parameter essentially is a list of numbers. As I have found, DAX have no "IN" operator, so how can I handle this case?

Upvotes: 2

Views: 1079

Answers (1)

Kalim
Kalim

Reputation: 485

This is a bit more tricky to do, and it's not something ive tried to do yet. However a quick google search brings up some decent results. This particular blog write up by Chris Webb is quite comprehensive and helpful. You will need to adapt your DAX expression to use the pathcontains DAX function and you will also need to change your SSRS parameter to a multi parameter and do some string manipulation. See here http://blog.crossjoin.co.uk/2012/06/01/handling-ssrs-multi-valued-parameters-in-dax-queries/

Upvotes: 3

Related Questions