I knew for a long time that measures can have text and not only numbers, but last week I had a scenario where it was the perfect solution for my problem.
Let's say that I work in number of jobs. In every job I got some tasks that I need to do. The fact table looks like this:
|
Job |
Task |
Tasks To Do |
Tasks Completed |
|
Developer |
Develop BI System |
1 |
1 |
|
Developer |
Help Friends |
1 |
0 |
|
Manager |
Manage |
1 |
1 |
|
Therapist |
|
0 |
0 |
Summarizing the "Tasks To Do" column will get the total tasks that I need to do. Summarizing the "Tasks Completed" column will get the tasks that I did. Note that in the Therapist job I didn't get any tasks.
In the OLAP cube, I created a calculated measure which is the percent of the completed tasks (for every job or for any other dimension). The problem is: What is the answer in case that there are no any tasks to do? Some will say 0% and some will say 100%. That's why a text measure such as "There are no tasks" can be a perfect solution for this kind of problem. This is the calculation of the calculated measure:
Create Member CurrentCube.[Measures].[Completed Percentage] as
IIF(IsEmpty([Measures].[Tasks To Do]),
null,
IIF([Measures].[Tasks To Do] = 0),
'There are no tasks',
[Measures].[Completed Tasks] / [Measures].[Tasks To Do]
)
)
This scenario can happen in many cubes. Tell me if you have any other solution for this kind of problem.
Update: Read the important comments below.