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

Grouping over multiple datasets

Hi, I was wondering if someone could help.

I have a dataset (Table A) that has actual consumption data in (meaning I cant change) and there is a column in this dataset I need to group for a visual.

I have created a reference table with all IDs and names (Table B) that I want to use to group the column in Table A. The reason for this is that some IDs may not yet consume anything and I want to ensure that if they do in future its automatically mapped correctly.

My question is: How do you group a column in Table A based on reference information in Table B? Bare in mind that 2 different IDs may group into an overarching name

Thanks!
1 ACCEPTED SOLUTION

Hello,

 

Thanks again for getting back to me. I did some digging and I have solved my problem! 

 

I read this post https://community.powerbi.com/t5/Desktop/How-to-replicate-Vlookup-in-power-BI/td-p/159810

 

Which then directed me to this page:

 

https://docs.microsoft.com/en-us/dax/lookupvalue-function-dax

 

Which I was able to impliment into my model and effectively 'group' data by using a reference table, creating a relationship and then running the look up to give me new values I used in my visuals. 

 

I'm very happy that I was able to make some formula work as normally I don't understand it all! (learning at I go here)

 

Thank you very much again. 

 

View solution in original post

5 REPLIES 5
Super User II
Super User II

Hello @ET_Phone2 

Do you mean you have a 2 part ID, like State & City?  If so, you can add a caclulated column to both tables that combines the key fields into 1 then join the tables on that combined field.

Hi!

Thanks for responding. I was thinking of more of a VLOOKUP:

"If you find X in the column then throw back Y from the referance"

I would then use the new column for the visual.

Would that be a calculated column? I'm assuming it would calculate everytime new data is added?

Thanks

@ET_Phone2 , Can you please share some sample data and show your expected result?  If you paste the data from excel directly into your post rather than a picture it makes it easier to work with also.

Hello,

 

Thanks again for getting back to me. I did some digging and I have solved my problem! 

 

I read this post https://community.powerbi.com/t5/Desktop/How-to-replicate-Vlookup-in-power-BI/td-p/159810

 

Which then directed me to this page:

 

https://docs.microsoft.com/en-us/dax/lookupvalue-function-dax

 

Which I was able to impliment into my model and effectively 'group' data by using a reference table, creating a relationship and then running the look up to give me new values I used in my visuals. 

 

I'm very happy that I was able to make some formula work as normally I don't understand it all! (learning at I go here)

 

Thank you very much again. 

 

View solution in original post

Hello @ET_Phone2 

Excellent!  Glad you were able to find a solution that works for you and thank you for letting us know and sharing the links.

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors