cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Hashiru
Helper I
Helper I

How to Query a Field with long list of Items to Exclude a few Items

I all,

 

I have a DAX Query to filter my dataset to a subset for further analysis. However, the list of Items in the field is long and I am seeking a better way to do this. Here is the query I got so far

 

EVALUATE
FILTER(
       PreviousData,
       AND(
            AND(
                PreviousData[System Source]="BP" ||PreviousData[System Source]="BL" ||   PreviousData[System Source]="BL" ,
                PreviousData[Type]="OL" ||PreviousData[Type]="UF" ||  PreviousData[Type]="YB"

               ),
            AND(
                PreviousData[Activity]="Alpha" ||PreviousData[Activity]="Beta" ||   PreviousData[Activity]="Labda" ,
                PreviousData[Fund]="2016" ||PreviousData[Fund]="2018" ||  PreviousData[Fund]="2018"

               )
       )
       //NOT(LEFT([Activity],5)="Any item")
)

 

The list goes in the third AND Function in the query for Activity and Fund with at least 50 items between them.

 

Thanks

2 REPLIES 2
Anonymous
Not applicable

Is it an option to use Power Query?  If so, I'd try to go that route or else you'll be typing forever and it will more than likely be slow 

Thanks Nick_M,

 

I like your suggestion, however, I am modeling for a whole department and the same dataset will have to be tweaked in different ways and the power query method will be to costly in terms of memory. I have cumulative data five years plus in a big organization. (I guess Power Query Table.SelectRows will do it for this situation but to many table will be created).

 

I am also exploring Power BI DAX IN function (Released December 2018). I tried it with a few items it worked. I am also going to try it in Power Pivot (as I am also creating same in Excel) when IT update my office.

 

 

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!