cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
joschultz Member
Member

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 these.  What is the best way to go about this?

 

Thank you,

 

Joseph

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

Re: Filter

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.

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

11 REPLIES 11
Super User IV
Super User IV

Re: Filter

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!


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

joschultz Member
Member

Re: Filter

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

 3-1-2016 12-32-10 PM.jpg

 

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

 

 

 

 

Super User IV
Super User IV

Re: Filter

@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!


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Twan Regular Visitor
Regular Visitor

Re: Filter

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

joschultz Member
Member

Re: Filter

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

joschultz Member
Member

Re: Filter

3-1-2016 12-30-03 PM.jpg

Highlighted
Twan Regular Visitor
Regular Visitor

Re: Filter

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

Re: Filter

Would I do that in a Calculated Column?

Super User III
Super User III

Re: Filter

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.

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors