Forum Replies Created
-
Posts
-
Sorry… the first equation should be:
=IF([quarter_text)=”Q4″, [year_no]+1, [year_no])
not minus one. You’re basically shoving every years fourth quarter into the future year.
And the second equations should be:
=IF([quarter_text]=”Q4″,”Q1″,”Q”&INT(Right([quarter_text],1)+1)) which switches Q1 and Q4 and replaces the minus with a plus.
Sorry, I did these quickly without checking my work. : (
I did it this way:
I added two calculated columns, one called Fiscal_Year and one called Fiscal_Quarter. Because your dates are in perfect alignment with the regular quarters (some companies can have first quarter start mid-month), I just shifted everything back one quarter.
Fiscal_Year: =IF([quarter_text)=”Q4″, [year_no]-1, [year_no])
Fiscal_Quarter: =IF([quarter_text]=”Q1″,”Q4″,”Q”&INT(Right([quarter_text],1)-1))
But I’m sure Tom Allen’s answer is correct too, as he is a wizard and is usually right. : )
The fact that they all are associated to wotask_ids that are higher than 728324 is great insight. I think that is definitely the first clue to figuring out what is going on.
What I’m almost more interested in knowing is how you came up with your tables that count the occurences. Did you do it with DAX? I looked in the background (PP) of your file but there was no model.
Sorry… try this: data model.
Tom,
I was able to solve my problem by passing the slicer value back into PowerPivot via a single-celled linked table and then passing the selection value into the main measures in all the fact tables (hope that makes sense). : )
Sense then, I have stripped the app down to basically just the data model to make the modificationsyou suggested You can find it here. I got rid of a fact table (we weren’t using it) and I added all the proj_wotask_id fields and relationships, and I made the dim_project table to dim_wotask_es table relationship active.
I took a snapshot of the pivot table found on the Test worksheet before all these changes and then refreshed it after the changes. You will see (in red) that some of the values are now coming back different.
Any ideas?
Thanks,
Jerid
OK. So I think that I understand. So, filtering in the PowerPivot window will not actually filter the dataset that my pivots are utilizing. It’s crazy to think that I built this big complicated model but overlooked something that critical. LOL. Maybe it’s a testament to how great PP is, as it’s obvious how powerful it can be with little expertise. : )
https://endeavourspacelaunch.files.wordpress.com/2011/05/monkey-astronaut.jpg?w=500
So, how do I achieve the desired end result? I alluded to some “interesting stuff” in an earlier post. One of those is my work around for essentially passing values from Excel cells into PP and using them in DAX formulas. I do it already in this app with a calculated column called [LessThan_RepMonth]. So, I create a single cell linked table in Excel out of the value I want to pass, and that brings it into PP as a table. Then create a measure in that table that points at the new single celled table. And then I reference that measure like this: IF([month_no]<(ALL(Report_Month)[DAX_RM])), TRUE(), FALSE()) as a calculated column.
I don’t know if this is brilliant or really stupid. I may be working around an obvious and intended real solution. I don’t know. But the beauty of this is that when the value is changed in Excel (via dropdown), this “variable” is passed to PP and all my measures automagically change.
Is this a viable solution or do you suggest something else?
Alan,
Great. I just looked at it and I see and understand what you did with the concatenated key. I still don’t see the result that I would expect.
Let me explain… when I go to the Business_Analyst_Text field of the dim_WOTask_ES table and filter it for a particular Business Analyst (e.g. Jones, Peggy) and then flip over to the fact_ActualsOrders_ES, I expect all the measures their to change. For example, my expectation is that TotActuals and YTDActuals will change to represent numbers only associated with Peggy Jones records.
Does that make sense or am I missing something? Also, it seems that you added a bunch of calculated columns to count the # of rows in related tables. Could you explain those? Was that back-checking for accuracy? Also, the Related_Column calculated column seems to be for testing. But all of them are filtered; should they be?
Thanks,
Jerid
Tom,
Thanks so much for the insights. When you say that you “added” calculated columns though, do you mean that you actually implemented these changes to the file? If so, I don’t see any changes to the data model and I don’t see any new version of the file in the cloud drive.
I’m excited to try to implement your suggested changes, but I gotta be sure what file we’re working from.
Thanks,
Jerid
Tom,
First of all, thanks for the compliment. It’s not often that I get feedback from someone that understands these technologies. Feels good to get a little peer review. : ) It also does a lot of things that you can’t just see by looking at it. Some interesting stuff if your interested.
Anyways, I tried to implement your suggested design change to the data model but I wasn’t able to get it to filter the fact tables, as needed. When you say, “WO_Task_ID numbers are not unique across projects” are you asking if there is only one record in the dimProject table associated with each WO_Task ID? It seems that you are suggesting the creation of a concatenated key by making a calculated column in both tables?
I put the file with the modified data model: here.
Thanks,
Jerid
