Forum Discussion

joschultz's avatar
joschultz
Advocate II
10 years ago
Solved

Filter

    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 t...
Need Help
  • ImkeF's avatar
    ImkeF
    10 years ago

    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.

Twan's avatar
Twan
Advocate IV
10 years ago

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"
)