Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Hi @Buzz1126 ,
Is your expected result like the image below?
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.
Ok. Is it possible to set it to SSAS so I can modify relationships in Power BI w/o changing the underlying cube data?
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?
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
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
User | Count |
---|---|
97 | |
87 | |
77 | |
67 | |
63 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
59 |