cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Buzz1126
Helper III
Helper III

Grouping Account Locations (States) by Salesmen

Hi everyone, quick question.

 

I am trying to group a list of accounts by their corresponding salesman.  Each account has a location(state) and that is how you know which salesman the account belongs to.

 

For instance, Apple Inc. is in California, and therefore it would belong to Matt.  Home Depot is in Georgia, and Georgia belongs to Connor.  There are only 3 salesement, thousands of accounts, and 50 states!

 

DimAccount[ParentAccountName] - List of Accounts

DimAccount[State] - List of State Abbreviations

 

I'm connected to a live Analysis Services Database, not sure this matters.

 

Thank you.

16 REPLIES 16
v-eachen-msft
Community Support
Community Support

Hi @Buzz1126 ,

 

Is your expected result like the image below?

6-1.PNG

If your result is from different tables, you need to configure relationships between them.

But I noticed that you only have a report tab in your screenshot, so I think you used live connection. It means you cannot set relationships in Power BI and you need to set it in SSAS.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

Ok.  Is it possible to set it to SSAS so I can modify relationships in Power BI w/o changing the underlying cube data?

jstorm
Resolver III
Resolver III

Make a "lookup" table with the 50 states listed in a 'states' column and the corresponding salesmen in a 'salesmen' column. Then add a relationship to your state table.  Each state will then be associated with a salesman.

Thank you for your reply.  I unfortunately am not able to create relationships as I am connected to Live data.

So there are three ways of connecting to the data named "Live Connection", "Direct Query", and "Import".  Live connection does not allow any manipulation of the data and generates visuals for you.  Direct Query queries the database directly for all information and can be very slow but the data is current.  Import is the fastest and allows maximum manipluation of the data.  It sounds like you are using a live connection.  If that is the case, there isn't anything you can do. I would recommend switching to Direct Query or Import so you can transform and manipulate the data.  Live conection is only necessary when you need real-time data.  Direct Query is best for near real-time (within hours).  Import is the best when the data can be up to 1 day old.

Thank you!  Sorry for being vague, after reading the article I am on a direct query.  Not a live connection.  I'm able to make measures, stuff like that.

No problem at all, just want to make sure we are on the same page.  I believe you should be able to make a lookup table and create relationships with direct query unless you are working with OLAP.  Are you familiar with creating relationships in the model tab?

I have used the relationship tab before, but under direct query it is greyed out.  The only thing selectable on the modeling tab is New Measure

Can you post a screenshot of the greyed out areas you are seeing?

Capture.PNG

So that is actually the Modeling section of the ribbon, but I am refering to the model view showing your data model here:

 

https://docs.microsoft.com/en-us/power-bi/desktop-relationship-view

 

Please take a look and see if you can find your tables and add relationships this way.

I only see the top button, report.  There's nothing below it

Capture2.PNG

Okay, this is likely because of how your SSAS is set up.  I have limited expertise in loading tabular data but I would suggest reaching out to your team that created the SSAS cube to see if they can provide the right dimension for you to use.  I would also double check to see that you are using Import instead of Live connection.  Live Connection runs entirely off of the SSAS dimensions while Import should allow you to manage the tables and relationships in Power BI, independent of SSAS.  Essentially, SSAS and PBI both offer data modelling capabilities, but ideally the data should be modeled in one or the other, not both.  Additionally, refer to these resources for more information:

 

https://www.youtube.com/watch?v=7FgCfnjteuM

 

https://docs.microsoft.com/en-us/power-bi/desktop-ssas-multidimensional

 

https://docs.microsoft.com/en-us/power-bi/desktop-analysis-services-tabular-data

Thank you I'll keep trying.  It's always a dead end reaching out to IT lol.  But do you think it's  possible w/o modifying the SSAS Cube?

I would guess that the dimensions already exists and the cube doesn't need to be modified, so you could try going through the data source steps again to see if it is there.  Also, if you try loading the source a second time, you could check it is being loaded as import and not live, or if there was a step you skipped.  Beyond this, you're going to have to work with your IT department haha.  It may be as easy as a permissions issue, where they need to give you permission to access the model through PBI.  Best of luck!

Please read this article explaining the differences between live, direct, and import.

 

https://docs.microsoft.com/en-us/power-bi/desktop-directquery-about

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Dev Camp Session 30

Ted's Dev Camp - January 26, 2023

This session will provide guidance and teach campers the skills required to build Power BI reports that support multiple languages.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.