I need to create a measure on ssas using MDX to count employee number but I really don't have any idea how to convert an sql script to MDX, it's really complicated.
What I want is a measure that look like this:
CREATE MEMBER CURRENTCUBE.[Measures].[Employee_Number]
AS (count([Collaborateur].[Matricule]) ,
VISIBLE = 1;
This is my sql code:
SELECT a.Affectation,a.code_agence
(
SELECT COUNT(b.MATRICULE)
FROM [dbo].[ODS_HR_AFFECTATION] b
WHERE
a.[CODE_AFFECTATION] = b.CODE_AFFECTATION and
b.[DATE_FIN] = '2050-01-01'
) AS employee_count
FROM [dbo].[ODS_HR_AFFECTATION] a
Result
AFFECTATION CODE_AGENCE employee_count
Agence Megrine 1012 9
Agence Centrale 1000 9
Agence Bardo 1032 8
Agence hellel 1064 5
Agence Saloul 1061 5
Agence Ville 1079 5
Agence Aazira 1055 5
Agence batb bhar 1054 5
Agence Hammam 1093 5
Agence Midoun 1100 3
Agence May 1121 3
AGENCE KIT EDDAIER 1046 3
Agence Beb 1108 3
Agence Metouia 1120 3
Agence Mourouj 6 1128 3
Agence Metlaoui 1122 3
Welcome to StackOverflow. If i understand your problem correctly, you want to group by Affectation and code_agence and count the distinct MATRICULE for '2050-01-01'. For this purpose you want to create a measure, that will help you count the MATRICULE for any date or other grouping. Based on this understanding here is how you can solve it.
In your fact table you should have column, which contains the MATRICULEID. Now in your SSAS project you should create a Distinct count Measure on this column. Now other dimesnions like date and ODS_HR_AFFECTATION are connected to your fact, so what ever value you select for them the new measure will report the distinct count of MATRICULE for that cube space.
The query will be like.(Plz note this is just a sample, in your cube the name of the objects will be diffrent)
select [measures].[DistinctMATRICULE] on 0,
([DimODS_HR_AFFECTATION].[Affectation].[Affectation],
[DimODS_HR_AFFECTATION].[code_agence].[code_agence]) on 1
from [yourcube]
where
[Dimdate].[DATE_FIN].[2050-01-01]