Use a measure to check a column for an array/list of values on a live analysis server connection

[murrayb8]

Hi I was wondering if someone could help me here as I cant seem to find a way to do this;

I have a live connection to an SQL server analysis server database which has the following headers for example:

ID  Description Title

I want to be able to filter the views on my page to a list of IDs, well say 60 IDs out of 10K+ so manually this isnt really an option.

Ideally what I would like to do is have a measure that I can add to a table that checks the ID against each element of an array or list defined inside the measure so I can toggle the table to only the selected columns. Im imagining something like this:

My IDs Measure = IF(ISBLANK(CHECKFOR(<COLUMN TO CHECK>,ARRAY(ID1,ID2,ID3,etc))),"Not in my List","In my List")

Is something like this possible or does anyone know a way around this by any chance?

Thanks in advance!

[nickchobotar · Accepted solution]

@murrayb8

In DAX we can declare lists and tables as a variable. In this example, I create a list of currency keys as a variable and identify them in the table with a measure. Similarly, you could make this more dynamic by creating a separate ID table and call it inside the variable 

My IDs Measure =
VAR MyList = { 1, 2, 3, 4 } RETURN
    IF (
        CALCULATE (
            COUNTROWS ( 'Currency' ),
            FILTER ( 'Currency', 'Currency'[CurrencyKey] IN MyList )
        )
            = 1,
        "In My List",
        "Not in My List"
    )

*** there is also an option for you to create the same logic as a calc column and then you toggle between the full list and My List



Thanks, Nick -

[Diz]

I am doing something similar however I need to reuse the array in multiple measures.  Is there a way I can define it once and then use it in multiple measures?

I'd like to do this so that if (invariably "when") the array values change, I only need to update the definition once rather than maintaining it inside multiple measures and risking a human error on update?

Thanks,

-Diz

[murrayb8]

Just realised I never thanked you for this, thanks @nickchobotar!

[nickchobotar]

@murrayb8

I am glad to hear it worked out for you.

N -