Forum Discussion
Grouping over multiple datasets
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!
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.
5 Replies
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 , 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.
