cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
satindenave
Frequent Visitor

Dynamic INDEX/RANK creation using DAX

ORIGINAL DATA
CompanyDepartmentJob LevelScore
C1D5Executive Management0.64
C1D4Executive Management0.17
C1D2Executive Management0.06
C1D1Middle Management0.86
C1D5Junior Management0.64
C1D4Junior Management0.95
C1D4Junior Management0.17
C1D1Junior Management0.37
C1D5Executive Management0.27
C1D3Junior Management0.05
C1D5Senior Management0.97
C1D3Senior Management0.42
C2D4Senior Management0.68
C2D1Middle Management0.68
C2D2Junior Management0.17
C2D1Junior Management0.46
C2D4Middle Management0.24
C2D4Junior Management0.85
C2D4Junior Management0.88
C2D1Senior Management0.50
C2D3Executive Management0.61
C2D4Junior Management0.15
C2D1Junior Management0.65
C2D1Senior Management0.12
C2D5Executive Management0.10
C2D4Middle Management0.52
C3D4Junior Management0.61
C3D5Senior Management0.35
C3D4Junior Management0.73
C3D2Junior Management0.29
C3D3Junior Management0.27
C3D2Junior Management0.97
C3D4Executive Management0.65
C3D5Middle Management0.81
C3D1Middle Management0.39
C3D2Junior Management0.32
C3D4Middle Management0.07
C3D1Junior Management0.96
C3D4Middle Management0.33
C3D3Middle Management0.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
CompanyDepartmentJob LevelScoreIndex Column 1
C1D1Middle Management0.861
C1D1Junior Management0.372
C1D2Executive Management0.061
C1D3Senior Management0.421
C1D3Junior Management0.052
C1D4Executive Management0.171
C1D4Junior Management0.952
C1D4Junior Management0.173
C1D5Executive Management0.641
C1D5Executive Management0.272
C1D5Senior Management0.973
C1D5Junior Management0.644
C2D1Senior Management0.503
C2D1Senior Management0.124
C2D1Middle Management0.685
C2D1Junior Management0.656
C2D1Junior Management0.467
C2D2Junior Management0.172
C2D3Executive Management0.613
C2D4Senior Management0.684
C2D4Middle Management0.525
C2D4Middle Management0.246
C2D4Junior Management0.887
C2D4Junior Management0.858
C2D4Junior Management0.159
C2D5Executive Management0.105
C3D1Middle Management0.398
C3D1Junior Management0.969
C3D2Junior Management0.973
C3D2Junior Management0.324
C3D2Junior Management0.295
C3D3Middle Management0.564
C3D3Junior Management0.275
C3D4Executive Management0.6510
C3D4Middle Management0.3311
C3D4Middle Management0.0712
C3D4Junior Management0.7313
C3D4Junior Management0.6114
C3D5Senior Management0.356
C3D5Middle 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 2
CompanyDepartmentJob LevelScoreIndex Column 1Index Column 2
C1D2Executive Management0.0611
C1D4Executive Management0.1712
C1D5Executive Management0.6413
C1D3Senior Management0.4214
C1D1Middle Management0.8615
C1D5Executive Management0.2726
C1D1Junior Management0.3727
C1D3Junior Management0.0528
C1D4Junior Management0.9529
C1D5Senior Management0.97310
C1D4Junior Management0.17311
C1D5Junior Management0.64412
C2D2Junior Management0.1721
C2D3Executive Management0.6132
C2D1Senior Management0.5033
C2D1Senior Management0.1244
C2D4Senior Management0.6845
C2D5Executive Management0.1056
C2D1Middle Management0.6857
C2D4Middle Management0.5258
C2D4Middle Management0.2469
C2D1Junior Management0.65610
C2D1Junior Management0.46711
C2D4Junior Management0.88712
C2D4Junior Management0.85813
C2D4Junior Management0.15914
C3D2Junior Management0.9731
C3D3Middle Management0.5642
C3D2Junior Management0.3243
C3D2Junior Management0.2954
C3D3Junior Management0.2755
C3D5Senior Management0.3566
C3D5Middle Management0.8177
C3D1Middle Management0.3988
C3D1Junior Management0.9699
C3D4Executive Management0.651010
C3D4Middle Management0.331111
C3D4Middle Management0.071212
C3D4Junior Management0.731313
C3D4Junior Management0.611414

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
1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

@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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

@satindenave - Glad to hear it!


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors