ORIGINAL DATACompanyDepartmentJob LevelScoreC1D5Executive Management0.64C1D4Executive Management0.17C1D2Executive Management0.06C1D1Middle Management0.86C1D5Junior Management0.64C1D4Junior Management0.95C1D4Junior Management0.17C1D1Junior Management0.37C1D5Executive Management0.27C1D3Junior Management0.05C1D5Senior Management0.97C1D3Senior Management0.42C2D4Senior Management0.68C2D1Middle Management0.68C2D2Junior Management0.17C2D1Junior Management0.46C2D4Middle Management0.24C2D4Junior Management0.85C2D4Junior Management0.88C2D1Senior Management0.50C2D3Executive Management0.61C2D4Junior Management0.15C2D1Junior Management0.65C2D1Senior Management0.12C2D5Executive Management0.10C2D4Middle Management0.52C3D4Junior Management0.61C3D5Senior Management0.35C3D4Junior Management0.73C3D2Junior Management0.29C3D3Junior Management0.27C3D2Junior Management0.97C3D4Executive Management0.65C3D5Middle Management0.81C3D1Middle Management0.39C3D2Junior Management0.32C3D4Middle Management0.07C3D1Junior Management0.96C3D4Middle Management0.33C3D3Middle Management0.56I 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 departmentThe 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 1CompanyDepartmentJob LevelScoreIndex Column 1C1D1Middle Management0.861C1D1Junior Management0.372C1D2Executive Management0.061C1D3Senior Management0.421C1D3Junior Management0.052C1D4Executive Management0.171C1D4Junior Management0.952C1D4Junior Management0.173C1D5Executive Management0.641C1D5Executive Management0.272C1D5Senior Management0.973C1D5Junior Management0.644C2D1Senior Management0.503C2D1Senior Management0.124C2D1Middle Management0.685C2D1Junior Management0.656C2D1Junior Management0.467C2D2Junior Management0.172C2D3Executive Management0.613C2D4Senior Management0.684C2D4Middle Management0.525C2D4Middle Management0.246C2D4Junior Management0.887C2D4Junior Management0.858C2D4Junior Management0.159C2D5Executive Management0.105C3D1Middle Management0.398C3D1Junior Management0.969C3D2Junior Management0.973C3D2Junior Management0.324C3D2Junior Management0.295C3D3Middle Management0.564C3D3Junior Management0.275C3D4Executive Management0.6510C3D4Middle Management0.3311C3D4Middle Management0.0712C3D4Junior Management0.7313C3D4Junior Management0.6114C3D5Senior Management0.356C3D5Middle Management0.817 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 2CompanyDepartmentJob LevelScoreIndex Column 1Index Column 2C1D2Executive Management0.0611C1D4Executive Management0.1712C1D5Executive Management0.6413C1D3Senior Management0.4214C1D1Middle Management0.8615C1D5Executive Management0.2726C1D1Junior Management0.3727C1D3Junior Management0.0528C1D4Junior Management0.9529C1D5Senior Management0.97310C1D4Junior Management0.17311C1D5Junior Management0.64412C2D2Junior Management0.1721C2D3Executive Management0.6132C2D1Senior Management0.5033C2D1Senior Management0.1244C2D4Senior Management0.6845C2D5Executive Management0.1056C2D1Middle Management0.6857C2D4Middle Management0.5258C2D4Middle Management0.2469C2D1Junior Management0.65610C2D1Junior Management0.46711C2D4Junior Management0.88712C2D4Junior Management0.85813C2D4Junior Management0.15914C3D2Junior Management0.9731C3D3Middle Management0.5642C3D2Junior Management0.3243C3D2Junior Management0.2954C3D3Junior Management0.2755C3D5Senior Management0.3566C3D5Middle Management0.8177C3D1Middle Management0.3988C3D1Junior Management0.9699C3D4Executive Management0.651010C3D4Middle Management0.331111C3D4Middle Management0.071212C3D4Junior Management0.731313C3D4Junior Management0.611414Please 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 outcomeSelect 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 #tempFROM DimJobLevelselectcompany,department,job_level,score,index_column_1,ROW_NUMBER() over(partition by company order by company) as index_column_2FROM (Selectcompany,department,t1.job_level,t2.JOBLEVELCODE,score,ROW_NUMBER() over(partition by company, department order by company, t2.JOBLEVELCODE) as index_column_1FROM FactTbl t1 left join #temp t2on t1.job_level = t2.JOB_LEVELorder by company,department,t2.JOBLEVELCODE,score desc) aorder by company, index_column_1, JOBLEVELCODE

satindenave - These might help: https://community.powerbi.com/t5/Quick-Measures-Gallery/To-Bleep-with-RANKX/m-p/1042520#M452 https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Mythical-DAX-Index/td-p/1093214

satindenave

Frequent Visitor

6 years ago

Solved

Dynamic INDEX/RANK creation using DAX

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

DAX

Dynamic Index

Dynamic Ranking

Greg_Deckler
6 years ago
satindenave - These might help:

https://community.powerbi.com/t5/Quick-Measures-Gallery/To-Bleep-with-RANKX/m-p/1042520#M452

https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Mythical-DAX-Index/td-p/1093214

Forum Discussion

Dynamic INDEX/RANK creation using DAX

3 Replies

Related content

Dynamic Table Creation???

Dynamic route creation?

Dynamic Index/Rank (1st, 2nd, 3rd) on Graphs

RANK indexing with condition

Dynamic Filter creation based on multiple criteria