CODEWITHSUNDEEP
powerbidax
Guelque Hadrien
Guelque Hadrien

Reputation: 47

PowerBI: Using a measure to get a rank

I'm kind of new to PowerBi, but I haven't been able to find an answer to my problem.

I have this kind of dataset:

Timestamp       ErrorType   Duration (ms)
16/05/10 8:00   3           100
16/05/10 8:00   4           1000
17/05/10 10:00  3           100
18/05/10 8:00   3           200
18/05/10 10:00  4           200
18/05/10 10:00  5           50
19/05/10 10:00  5           10
19/05/10 10:00  5           10

The names are hopefully pretty self explanatory: Timestamp is the time at which the issue occured, the ErrorType is a code to know what kind of error that is, and the Duration indicates how long the issue lasted. What I'd like to do is essentially make a measure that would give me the rank of the error type, taking into account any filters I could use on the page.

For example, should I have restricted myself to the time period 17/05 to 19/05, the measure for 3 would give me 1, and the measure for 4 would give me 2, whereas that would be the opposite should I sample over all the time scale. In the all cases, the measure for 5 would give me 3. The first case is illustrated in the table down below:

ErrorType   Rank
3           1
4           2
5           3

The idea behind this is to be able to color code a graph using their importance in the specified time frame. For example, for error that lasted the longest (cummulative) on the specified time frame, then it shall be colored red; orange for the second; and all that for the first eight. That part I know how to do.

I've already tried something, but it just won't work, for some reason it says that it can't find the column called "Total" in the Table I've just created...

Color = 
VAR TotalTime = CALCULATE(
    SUM('AuFilDeLEau'[Duration]),
    ALLSELECTED('AuFilDeLEau'[ErrorCode])
)
VAR ColumnTotalTime = (SUMMARIZE(Table, Table[Duration], "Total", SUM(Table[Duration])))
VAR RankforColor = (RANKX(ColumnTotalTime, [Total],TotalTime))
return RankforColor

Currently, this only gives me back 1 for each and every ErrorType. I hope my issue is clear, and you'll be able to help me, thanks in advance ^^

EDIT: tried smpa01's solution, didn't work, it did this:

enter image description here

EDIT: tried smpa01's second solution, it worked. Marked as solved. Thanks !

Upvotes: 0

Views: 530

Answers (1)

smpa01
smpa01

Reputation: 4282

_rank = 
VAR _1 =
    MAX ( 'Table'[ErrorType] )
VAR _2 =
    RANKX (
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[ErrorType] = _1 ),
        CALCULATE ( MAX ( 'Table'[Duration] ) ),
        ,
        ASC,
        DENSE
    )
RETURN
    _2

Solution

EDIT : Adding this portion after revised data

_revisedRank = 
IF (
    HASONEVALUE ( 'Table'[ErrorType] ) = TRUE (),
    RANKX (
        ALL ( 'Table'[ErrorType] ),
        CALCULATE ( SUM ( 'Table'[Duration (ms)] ) ),
        ,
        DESC
    )
)

Solution

Upvotes: 1

Related Questions