User Profile
satindenave
Frequent Visitor
Joined 6 years ago
User Widgets
Contributions
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, JOBLEVELCODESolved1.7KViews0likes3Comments
Data Privacy
Microsoft Fabric Community and Privacy
To learn more about how we manage your data, please review the Microsoft Fabric Community Data Privacy guide.