Filter

[joschultz]

I have a column that I need to group by an ID number.  I have a list of 25 IDs that I need to split into two groups  but then would like the rest put into a third group so that I can slice on these.  What is the best way to go about this?

Thank you,

Joseph

[Greg_Deckler]

Without knowing anything about your data model, hint, hint, you could brute force it with a HUUUUGE IF statement in a calculated column like:

IF([ID]=xxxx,"Group 1",IF([ID=yyyy,"Group 1",IF([ID]=zzzz,"Group 2","Group Other")))

Probably a better way, but I would have to see some sample data!

[Twan]

Kind of off topic, but an easier way to solve huge IF statements is with a SWITCH statement.  Then you don't have to worry about so many parentheses.  Your IF statement would like this:

=
SWITCH (
    TRUE (),
    [ID] = xxx, "Group 1",
    [ID] = yyy, "Group 2",
    [ID] = zzz, "Group 2",
    "Group Other"
)

[ImkeF · Accepted solution]

Performance is best if you do this task in the query editor:

Start in your sales table and merge it with the table containing the IDs for EDU and MIL on Offer Id. Expand the result on one field only: Portal.

This will allocate EDU and MIL to the matching items and leave null for all others. Then replace null by the name you want this group to be named.

[joschultz]

Thank you all!!  That was exactly what I was trying to do!  and it worked!

[joschultz]

Would I do that in a Calculated Column?

[joschultz]

So here is my table that I have for an EDU or Mil Portal. 

Then in my sales data I have a column called Item Offer ID

 

I want the Ids in the sales table to be either EDU or MIL based on the first table and then all other values in that sales data to be labled "general" so that I can slice off of those 3 categories.

Thank you,

JOseph

[Twan]

Can you post the picture of the first table again?  It isn't loading.

[joschultz]

[Twan]

@ImkeF's solution would be the best performing.  It is all done in the query editor.  Here is how to do it in the data model with DAX if you aren't familiar with the query editor (I'm still use to solving these problems in the same way I did without the query editor in PowerPivot).

1. Create a relationship between the two tables on the [Offer ID] columns.
2. Add a calculated column on the sales data table that holds the [Offer Type] field

   1. Portal = RELATED ('TableName'[Portal])

      You can also use the LOOKUPVALUE() function to get the [Offer Type] if you don't want to create a relationship.
3. Add a calculated column that groups the [Offer Types]. 

   1. Portal Type = SWITCH (
          TRUE (),
          [Portal] = "EDU", "EDU",
          [Portal] = "Military", "MIL",
          "General"
      )

[Greg_Deckler]

@joschultz - So, is there anything in the "Item Offer ID" that distinguishes an EDU versus a MIL or is it purely based on the Item Offer ID individually? If the latter, then your best bet is to build a table of the "Item Offer ID" and the category like:

Item Offer ID,Category

45322908501,EDU

45188472001,MIL

...

Then you can relate that table to this new table and Bob's your uncle!

[joschultz]

What distinguizhers it is the Item Offer Number.  I did create a table that has those for MIL and EDU for Ids but there are a bunch of other ones that I dont have that are in sales data  I would like to be under the group of General.  So that I can slice between the three categories.

IS there a way to create a table with the EDU and Mil offer ids and then have everything else fall under "general"?