Dynamic INDEX/RANK creation using DAX

satindenave — Mon, 10 Aug 2020 17:45:58 GMT

ORIGINAL DATA
Company	Department	Job Level	Score
C1	D5	Executive Management	0.64
C1	D4	Executive Management	0.17
C1	D2	Executive Management	0.06
C1	D1	Middle Management	0.86
C1	D5	Junior Management	0.64
C1	D4	Junior Management	0.95
C1	D4	Junior Management	0.17
C1	D1	Junior Management	0.37
C1	D5	Executive Management	0.27
C1	D3	Junior Management	0.05
C1	D5	Senior Management	0.97
C1	D3	Senior Management	0.42
C2	D4	Senior Management	0.68
C2	D1	Middle Management	0.68
C2	D2	Junior Management	0.17
C2	D1	Junior Management	0.46
C2	D4	Middle Management	0.24
C2	D4	Junior Management	0.85
C2	D4	Junior Management	0.88
C2	D1	Senior Management	0.50
C2	D3	Executive Management	0.61
C2	D4	Junior Management	0.15
C2	D1	Junior Management	0.65
C2	D1	Senior Management	0.12
C2	D5	Executive Management	0.10
C2	D4	Middle Management	0.52
C3	D4	Junior Management	0.61
C3	D5	Senior Management	0.35
C3	D4	Junior Management	0.73
C3	D2	Junior Management	0.29
C3	D3	Junior Management	0.27
C3	D2	Junior Management	0.97
C3	D4	Executive Management	0.65
C3	D5	Middle Management	0.81
C3	D1	Middle Management	0.39
C3	D2	Junior Management	0.32
C3	D4	Middle Management	0.07
C3	D1	Junior Management	0.96
C3	D4	Middle Management	0.33
C3	D3	Middle Management	0.56

I want to create two index column dynamically which reset itself based on the selection in slicer.
Index column 1:

This index column is sorted by Job Level and Score & partioned by company and department
The data should be sorted in the below specified format which is -
1. Company (alphabetically)
2. Department (alphabetically)
3. Job Level (Executive, Senior, Middle, Junior Management)
4. Score (Decreasing order)

Sorting Criteria:
1. If the Job Level is same within a department then sort the index on score.

DATA WITH INDEX COLUM 1
Company	Department	Job Level	Score	Index Column 1
C1	D1	Middle Management	0.86	1
C1	D1	Junior Management	0.37	2
C1	D2	Executive Management	0.06	1
C1	D3	Senior Management	0.42	1
C1	D3	Junior Management	0.05	2
C1	D4	Executive Management	0.17	1
C1	D4	Junior Management	0.95	2
C1	D4	Junior Management	0.17	3
C1	D5	Executive Management	0.64	1
C1	D5	Executive Management	0.27	2
C1	D5	Senior Management	0.97	3
C1	D5	Junior Management	0.64	4
C2	D1	Senior Management	0.50	3
C2	D1	Senior Management	0.12	4
C2	D1	Middle Management	0.68	5
C2	D1	Junior Management	0.65	6
C2	D1	Junior Management	0.46	7
C2	D2	Junior Management	0.17	2
C2	D3	Executive Management	0.61	3
C2	D4	Senior Management	0.68	4
C2	D4	Middle Management	0.52	5
C2	D4	Middle Management	0.24	6
C2	D4	Junior Management	0.88	7
C2	D4	Junior Management	0.85	8
C2	D4	Junior Management	0.15	9
C2	D5	Executive Management	0.10	5
C3	D1	Middle Management	0.39	8
C3	D1	Junior Management	0.96	9
C3	D2	Junior Management	0.97	3
C3	D2	Junior Management	0.32	4
C3	D2	Junior Management	0.29	5
C3	D3	Middle Management	0.56	4
C3	D3	Junior Management	0.27	5
C3	D4	Executive Management	0.65	10
C3	D4	Middle Management	0.33	11
C3	D4	Middle Management	0.07	12
C3	D4	Junior Management	0.73	13
C3	D4	Junior Management	0.61	14
C3	D5	Senior Management	0.35	6
C3	D5	Middle Management	0.81	7

Index column 2:
This column will be created after sorting the data by company, index column 1 and job level and partitioned by company.

DATA WITH INDEX COLUMN 2
Company	Department	Job Level	Score	Index Column 1	Index Column 2
C1	D2	Executive Management	0.06	1	1
C1	D4	Executive Management	0.17	1	2
C1	D5	Executive Management	0.64	1	3
C1	D3	Senior Management	0.42	1	4
C1	D1	Middle Management	0.86	1	5
C1	D5	Executive Management	0.27	2	6
C1	D1	Junior Management	0.37	2	7
C1	D3	Junior Management	0.05	2	8
C1	D4	Junior Management	0.95	2	9
C1	D5	Senior Management	0.97	3	10
C1	D4	Junior Management	0.17	3	11
C1	D5	Junior Management	0.64	4	12
C2	D2	Junior Management	0.17	2	1
C2	D3	Executive Management	0.61	3	2
C2	D1	Senior Management	0.50	3	3
C2	D1	Senior Management	0.12	4	4
C2	D4	Senior Management	0.68	4	5
C2	D5	Executive Management	0.10	5	6
C2	D1	Middle Management	0.68	5	7
C2	D4	Middle Management	0.52	5	8
C2	D4	Middle Management	0.24	6	9
C2	D1	Junior Management	0.65	6	10
C2	D1	Junior Management	0.46	7	11
C2	D4	Junior Management	0.88	7	12
C2	D4	Junior Management	0.85	8	13
C2	D4	Junior Management	0.15	9	14
C3	D2	Junior Management	0.97	3	1
C3	D3	Middle Management	0.56	4	2
C3	D2	Junior Management	0.32	4	3
C3	D2	Junior Management	0.29	5	4
C3	D3	Junior Management	0.27	5	5
C3	D5	Senior Management	0.35	6	6
C3	D5	Middle Management	0.81	7	7
C3	D1	Middle Management	0.39	8	8
C3	D1	Junior Management	0.96	9	9
C3	D4	Executive Management	0.65	10	10
C3	D4	Middle Management	0.33	11	11
C3	D4	Middle Management	0.07	12	12
C3	D4	Junior Management	0.73	13	13
C3	D4	Junior Management	0.61	14	14

Please remeber i need a measure which changes on the basis of slicer. There are 14 more filters to slice and dice the data so calculated column won't work.

I have created a SQL query to understand the desired outcome

Select distinct JOB_LEVEL, (case when JOB_LEVEL = 'executive management' then 1
when JOB_LEVEL = 'senior management' then 2
when JOB_LEVEL = 'middle management' then 3
when JOB_LEVEL = 'junior management' then 4
when JOB_LEVEL = '' then 0
end ) JOBLEVELCODE
into #temp
FROM DimJobLevel

select
company
,department
,job_level
,score
,index_column_1
,ROW_NUMBER() over(partition by company order by company) as index_column_2

FROM (

Select
company
,department
,t1.job_level
,t2.JOBLEVELCODE
,score
,ROW_NUMBER() over(partition by company, department order by company, t2.JOBLEVELCODE) as index_column_1
FROM FactTbl t1 left join #temp t2
on t1.job_level = t2.JOB_LEVEL
order by company
,department
,t2.JOBLEVELCODE
,score desc
) a
order by company, index_column_1, JOBLEVELCODE

Re: Dynamic INDEX/RANK creation using DAX

satindenave — Thu, 13 Aug 2020 06:40:51 GMT

Greg_Deckler Thanks for taking out your time to provide the solution. It worked for me with a small change in the DAX.

Re: Dynamic INDEX/RANK creation using DAX

Greg_Deckler — Thu, 13 Aug 2020 15:10:14 GMT

satindenave - Glad to hear it!

topic Dynamic INDEX/RANK creation using DAX in DAX Commands and Tips

Dynamic INDEX/RANK creation using DAX

Re: Dynamic INDEX/RANK creation using DAX

Re: Dynamic INDEX/RANK creation using DAX

Re: Dynamic INDEX/RANK creation using DAX