Archive for October, 2015

I am presented with a task to support an existing tabular cube for a client.

ssas tabular model

There is a bug within the report where the number of the individuals achieving their plans are incorrect because an individual can have more than one achieved plan. It is also incorrect because it used a measure that count the number of plans achieving goals instead of the number of individuals achieving goals.

As an example, the Individual ID of 47313 and 52478 each achieved two plans, so it should be counted as one participant instead of two.

An individual can have more than one plan

As you may have known, when you create a report in SSRS over a tabular cube, the dataset query is in MDX, instead of DAX. As I am so familiar with MDX, this task is a piece of cake, however, as my client is also using Pivot Table in Excel, you can’t really modify anything in there. So the tabular cube needs to be fixed and I still need to learn how to do this in DAX.  I needed to add another measure to count the total distinct individual achieving plan goals i.e. [Measures].[Total Individuals Achieving Goals] into the cube in DAX.



MEMBER [Measures].[Total Individuals Achieving Goals]
FILTER( [Participant].[Participant_SK].[Participant_SK].MEMBERS , [Measures].[Total Plans Achieving Goals] >= 1 )

MEMBER [Measures].[Total Individuals Not Achieving Goals]
FILTER( [Participant].[Participant_SK].[Participant_SK].MEMBERS , [Measures].[Total Plans Not Achieving Goals] >= 1 )
MEMBER [Measures].[Total Individuals Achieving Goals %] AS CASE WHEN [Measures].[Total Reviews] = 0 THEN 0 ELSE [Measures].[Total Individuals Achieving Goals] / [Measures].[Total Reviews] END

MEMBER [Measures].[Total Individuals Not Achieving Goals %] AS CASE WHEN [Measures].[Total Reviews] = 0 THEN 0 ELSE [Measures].[Total Individuals Not Achieving Goals] / [Measures].[Total Reviews] END

SELECT NON EMPTY { [Measures].[Total Reviews]
,[Measures].[Total Plans Achieving Goals]
,[Measures].[Total Plans Not Achieving Goals]
,[Measures].[Total Individuals Achieving Goals]
,NON EMPTY { [Participant].[CostCentreName].&[Cockburn Kwinana] } ON ROWS
FROM [Model]
WHERE ( [Participant].[IsCostCentre].&[Yes]
,[Review Date].[CurrentYTD].&[Yes]
,[Plan Details].[IsApproved].&[Yes]

Total Individuals Achieving Goals

As shown in the screenshot above, the report should show the total individuals achieving goals of 49 instead of 51, because two of the individuals achieved 2 plans.

At first thought, how do I convert my calculated measure of [Measures].[Total Individuals Achieving Goals]  in MDX query to DAX? I needed to filter how many participants have achieved their plan goals and then count them.

So I tried a straight conversion to DAX as shown below. It complains about semantic error of “The COUNT function only accepts a column reference as an argument”.



MEMBER [Measures].[Total Individuals Achieving Goals]  AS COUNT(FILTER( [Participant].[Participant_SK].[Participant_SK].MEMBERS , [Measures].[Total Plans Achieving Goals] >= 1 ))


Measure 1:=COUNT(FILTER(Plan[ParticipantID], [Total Plans Achieving Goals] >= 1))

DAX is very different from MDX so there is no simple conversion.

To do that in DAX, first count the number of distinct participants for the whole fact table and then filter how many of the distinct participants have achieved plan goals.

Total Participants:=DISTINCTCOUNT([Participant_SK])

Total Individuals Achieving Goals:=CALCULATE([Total Participants], ‘Plan Details'[PlanGoalsAchieved] = “Yes”)

Following the same logic:

Total Individuals Not Achieving Goals:=CALCULATE([Total Participants], ‘Plan Details'[PlanGoalsAchieved] = “No”)

Total Individuals Achieving Goals Percentage:=DIVIDE([Total Individuals Achieving Goals], [Total Reviews], 0)

Total Individuals Not Achieving Goals Percentage:=DIVIDE([Total Individuals Not Achieving Goals], [Total Reviews], 0)

Once the the table in the model has been processed (menu: Model -> Process -> Process Table), it will auto populate the selected table with data and deploy a copy of cube with your username included in the name. Once you are happy with the changes made, you can then deploy to overwrite the existing cube.

ssas tabular model 2

I also noticed that only the calculated measures will be listed under Measures in the tabular model. The “base” measures are listed under Dimensions. In multidimensional, all base and calculated measures appear under Measures. Furthermore, tabular does not support role playing dimensions. Say your sales fact table has got a few different dates such as OrderDate, DueDate, and ShipDate. You would need to import the Date table three times and then rename them including the meta data accordingly. You would then need to create the relationship between the sales table and date table.

You really need to change your thinking around DAX versus MDX and Tabular vs Multidimensional.

Read Full Post »

%d bloggers like this: