Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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....
Need Help
jdbuchanan71's avatar
jdbuchanan71
Super User
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago
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's avatar
    jdbuchanan71
    Super User
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        jdbuchanan71
        Super User
        7 years ago

        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.