cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
murrayb8 Regular Visitor
Regular Visitor

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

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!

1 ACCEPTED SOLUTION

Accepted Solutions
nickchobotar Established Member
Established Member

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

@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

image.png

Thanks, Nick -

View solution in original post

4 REPLIES 4
nickchobotar Established Member
Established Member

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

@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

image.png

Thanks, Nick -

View solution in original post

murrayb8 Regular Visitor
Regular Visitor

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

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

nickchobotar Established Member
Established Member

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

@murrayb8

 

I am glad to hear it worked out for you.

 

N -

Diz Frequent Visitor
Frequent Visitor

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

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

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,515)