This topic contains 74 replies, has 2 voices, and was last updated by 
-
Posts
-
Warren,
A common technique is to suppress grand totals and subtotals with measures is to use the “IF ( HASONEVALUE (” construction (see pages 137, 138 of DAX Formulas for Power Pivot” for a practical example and explanation of how and why it works). For example, the following would suppress subtotals and grand totals:
MyMeasureWillNotBeRolledUp :=
CALCULATE ( IF (
HASONEVALUE ( TableName[ColumnName] ),
SUM ( TableName[ColumnName] )
)
)Tom,
Thank you so much for all your help.
Warren,
As long as the dialog helps, let’s keep the conversation open.
Hi Tom,
I’m trying to update the linkback table to introduce the concept of maintenance start dates. I added the following line that’s bolded. Any thoughts on how I’d achieve this?
EVALUATE
SUMMARIZE(FILTER(CALCULATETABLE (CROSSJOIN (Tracking,Months)),
ISBLANK([Maintenance Cancellation Request Date])
||[Maintenance Cancellation Request Date]>date(2014,12,31)
||[Maintenance Start Date]>=[MonthStart]
),
[Frame SN],
[Monthly Maintenance],
[MonthStart],
[Cost Type]
)
ORDER BY[Frame SN],[MonthStart],[Cost Type]or… maybe this isn’t done in that table, my bad. My calculate cost post calculation measure does this now. I tried this, but it’s not working. Thoughts?
Cost Post Cancellation:=CALCULATE([Sum of Maintenance Cost], filter(Costs, ISBLANK(related(Tracking[Cancellation Effective Date]))||Costs[MonthStart]>=RELATED(Tracking[Maintenance Start Date]) && Costs[MonthStart]<related(Tracking[Cancellation Effective Date])))
I think I did it…. thoughts?
Cost Post Cancellation:=CALCULATE([Sum of Maintenance Cost],
filter(Costs, Costs[MonthStart]>=RELATED(Tracking[Maintenance Start Date])
&&Costs[MonthStart]<related(Tracking[Cancellation Effective Date])
||
ISBLANK(related(Tracking[Cancellation Effective Date]))
&&
Costs[MonthStart]>=RELATED(Tracking[Maintenance Start Date])))
Warren,
I also think you did it.
Makes sense and logic checks out.
Hi Tom,
Question for you. I have a situation where the Serial # we are tracking should be unique and should match between all sources of record, but they often don’t.
I assume this is a common issue. What’s the best way to handle it? A lookup table that lists the various forms of the serial # across the various sources?Hello Warren,
I want to make sure I understand the circumstances: the issue is that serial numbers do not match between the different data sources, not that there are duplicated serial numbers within a given data source.
Question: Is this primarily a problem between your company’s records and vendor records or are there multiple “internal” data sources that are using different serial numbers for the same system?
Question: How many different data sources are there?
Another question: If you have about 1300 records, about how many of the individual records have non-matching serial numbers?
Hi Tom,
It’s really all over the place. The SN on the system is one source. But the vendors may not use that completely and remove the first 2 digits and replace them with 00. The internal sources are manually entered and may or may not use the full serial #.
There are probably 3-4 sources of 3000 serial #s I’m tracking. There are some duplicates, but I can try to work around that or build a solution for that. I thought it might be useful to create some kind of table that builds a relationship between these different “unique” serial #s. 🙂
Warren,
To have such a lookup table sounds helpful as a reference to ensure other players are facing the same targets (have their eyes on the same goal) as you.
Be careful not to let such a lookup table interfere with the logic of your cost calculations, otherwise you could end up with a solution that is a headache to modify and maintain. I could possibly see an entry for the vendor’s serial number “reference” in the tracking table.
Warren,
How is your project?
Hi Tom,
Looks like I’m moving to a new job. So, I won’t be needing to use this. The good news is I read both of Rob’s books, Kasper’s, and watched all of Rob’s 22 hr course. 🙂
Warren,
Before you go, I am interested in learning about your new job…
What will you be doing?
Don’t forget in your list of credits you contributed (50% of comments) to one of the most read forum discussions on linkback tables in Excel and Power Pivot. Others learn from experience you share.
Thanks again for all of your help through out all of this. I’ve learned a lot.
I’ll be a Senior Global Program Manager for IBM’s Transition and Transformation Team. I hope I’ll need to use PowerPivot at some point. Also, VBA and my other Excel skills.
I’ll continue to be a member of the community and hopefully help others.
The forum ‘Power Pivot’ is closed to new topics and replies.