Grouping over multiple datasets

[Anonymous]

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!

[jdbuchanan71]

Hello @Anonymous 

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.

[Anonymous]

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

[jdbuchanan71]

@Anonymous , 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.

[Anonymous · 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. 

[jdbuchanan71]

Hello @Anonymous 

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