September 13, 2018 at 8:55 pm #10598
Hoping someone can have a look at the attached example model and help me get through this issue: how do I get the “many” side of my relationship to appear under the “one” side; displaying only *related* “many” records, instead of every “many” record under every “one” record.
I have seen some posts elsewhere about dealing with granularity differences, but was not able to implement…
Thanks in advance.
Attachments:You must be logged in to view attached files.September 14, 2018 at 3:12 pm #10604
I have looked at the workbook.
Your issue is really model design, not measures. One can easily create a check condition where the variance and the budget amount will only display when there is an actual amount. However, given your model, your totals will be wrong (this has a lot to do with that Power Pivot calculates totals by removing filters, not by evaluating other cells).
Your model would make more sense to me if the model started with actuals and budgets linked to a projects table.
TomSeptember 14, 2018 at 5:30 pm #10605
Hi Tom, thanks for looking at this. Could I trouble you to spell out how the revised model would look? I attempted to create a common table between them, but it seemed to become circular: since my Budget table would be one-to-one with the new Project table (on BudgetID), I would just end up effectively with a copy of my Budget table as the link between the ‘real’ Budget table and the Actuals table.September 15, 2018 at 10:19 pm #10618
Please see attached.
This is typical of how I organize project budget v. actual.
Attachments:You must be logged in to view attached files.September 15, 2018 at 10:53 pm #10620
I’ve been poking at this in the meantime, and discovered the secret sauce is the DAX measure that makes this work (IF>>NOT>>ISFILTERED…) even if you don’t breakout a new Projects (‘one’ side) table.
I’m curious on a couple points about your modelling habits. Do you exclusively add tables to your data model with power queries (and if so – why)? Do you have any other best practices that you normally follow?
Thanks for taking the time to look into this 👍September 16, 2018 at 1:15 am #10621
Many thanks for your kind words.
You can read more about ISFILTERED here.
Regarding the use of Power Query, in Excel 2016 and Excel 2013 I use it most of the time, but not always. For example, I find myself still importing a few tables from worksheets. There is something else called a linkback query, which is a DAX table query embedded in an Excel worksheet (not a pivot table). Somethings I do avoid as best I can: Excel 2010’s implementation of Power Query and (except for Excel 2010) importing directly through the power pivot window.
For best practices, I am keen on learning from Rob Collie, Matt Allington (Supercharge Power BI/Supercharge Excel) and Marco Russo/Alberto Ferrari, probably in that order.
What version of Excel are you working with? Do you also work with Power BI?
TomSeptember 16, 2018 at 11:59 am #10623
I am on Excel 2016, so I don’t have to worry about 2010 Power Query for now. Although I suspect the 2016 Power Query is the guilty party in some instability and file corruption fairly regularly. I’ve found that as great as Power Query and Power Pivot are, you can really pay a price in recovery time, as files corrupt and crash pretty frequently (still a big net win).
I always add tables and/or power queries from the Excel window (as opposed to the PP window) – sounds like that’s consistent with your habits.
I have tried and failed a few times on Power BI… my experience has several times been that: the behavior of things is not intuitive relative to what I’d expect in a pivot table. So my charts would always be all flat, or all maxed out, or otherwise not reflective of the analogous pivot table in Excel. So I’m still a pre-beginner in Power BI.
Also have read & benefited from Rob Collie and Avi Singh’s power pivot/power BI book.September 17, 2018 at 2:44 pm #10626
I encourage you to not give up on Power BI–we all learn through our failures or missteps.
The matrix in Power BI is similar, but not the same as the pivot table in Excel, especially the options for drill down (you can drill down to a lower level in a hierarchy with or without the data being filtered by the upper hierarchy levels).
If you have pbix files with sample data that you could attach, we could discuss and see why charts are falling flat or maxed out.
You must be logged in to reply to this topic.