Hi,
I'm creating a dimensional Framework and I can't find a solution to the following problem. I'm hoping you encountered the same problem and found a solution to it.
I'll try to explain with an example:
Year: 2009
Budget revenue: 100.000
Actual revenue:no fact records.
When I put this in a report, the following is shown:
Budget Actual
2009 100.000
When however I want to calculate the difference, it is also null because of the missing actual revenue value.
I know you can solve this in report studio by adding an if clause to the actual revenue measure, but I want it to be solved in FM, so then end user doesn't have to create the if clause for each report.
I tried:
- the missing value char (in FM and Report studio)
- adding the if clause in the measure in FM
- adding isnull and coalesce in the measure in FM
- adding the isnull, coalesce and even the if clause in the query subject
but nothing seems to solve this problem.
Any ideas?
Thanks a lot.
A dimensional / DMR model will always be based on a relational source. You need to make sure that you solve your problem there. There is a whole set of solutions, but we need to know a bit more (examples) to make a good suggestion.
[I have solved these issues with union constructions / cross joins etc, but the real deal is to fix it in the appropriate spot]