Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Everyone,
I am new to Power BI and using DAX. I need help with creating the appropriate Rankx expression for the following scenario:
Issues need to be ranked by two criteria: count (number of times issues have occured) and severity (S2 = highest rating and S0 = lowest rating).
Essentially, the issues column should be ranked by the following equation Count * Severity with severity rating weight as S2 = 4, S1 = 3, S0 = 2. The result should look as shown below:
Could someone please help me with writing the appropriate Rankx expression to obtain the result described above? I have made several attempts but still keep getting different results. Thank you!
Solved! Go to Solution.
Hey @mnll7!
I was able to get the ranking without creating another table; however, you have to create two measures: one to substitute the severity values with the numbers you indicated (e.g., 4, 3, 2) and the other for ranking.
Here is the Switch Measure:
MsrSevSwitch = IF( HASONEVALUE(Sheet1[Severity]), SWITCH(VALUES(Sheet1[Severity]), "S2",4, "S1",3, "S0",2 ),"" )
Here is the RANK Measure:
MsrRank = RANKX( ALL(Sheet1), CALCULATE( SUMX( Sheet1, [MsrSevSwitch] * Sheet1[Count ] ) ) ,,ASC,Dense )
Click HERE for my .pbix file.
Here is a screenshot of the table:
All,
Thank you for your support and answers.
This calculated column might be close
RAnk = VAR Sev = INT( REPLACE('Table2'[Severity],1,1,"")) VAR Sev2 = SWITCH( TRUE() , Sev=2,4, Sev=1,3, --ELSE-- 2) RETURN RANKX(ALL('Table2') , [Count] * Sev2)
Hey @mnll7!
I was able to get the ranking without creating another table; however, you have to create two measures: one to substitute the severity values with the numbers you indicated (e.g., 4, 3, 2) and the other for ranking.
Here is the Switch Measure:
MsrSevSwitch = IF( HASONEVALUE(Sheet1[Severity]), SWITCH(VALUES(Sheet1[Severity]), "S2",4, "S1",3, "S0",2 ),"" )
Here is the RANK Measure:
MsrRank = RANKX( ALL(Sheet1), CALCULATE( SUMX( Sheet1, [MsrSevSwitch] * Sheet1[Count ] ) ) ,,ASC,Dense )
Click HERE for my .pbix file.
Here is a screenshot of the table:
Hi @mnll7,
I created a second table with the severity rate ( called Rate) , link it to the principal table then I created a column for Count * Severity here we go:
Ranks= RANKX(ALL('Sample'),'Sample'[Count]*RELATED(Test[Coef]))
Ninter
I am new to Power BI and the use of DAX expressions. I have attempted to apply the appropriate Rankx expression for the following data shown below to rank the issues by count and severity only.
I would like to rank the issues by number of times it has occured (count) and by severity (S2 = highest rating and S0=lowest rating). Essentially, I am ranking by Count * Severity with the severity weight as S2 = 4, S1 = 3, S0 = 2. The end result should look as shown below:
Could someone please help me with writing the appropriate Rankx DAX expression to obtain the result described above? I have tried several rankx equations using measures and other functions within the rankx expression but no luck.
Thank you!
Hey @mnll7!
This is a duplicate post, and the other one has solutions posted to it. Can you please delete this one?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |