Forum Discussion

decarsul's avatar
decarsul
Helper V
5 years ago
Solved

Rank rows by 'unique' id

Good day all,

 

I'm back with another question, related to my last one.

Working on a set of data, where i want to rank rows based on 2 columns.

 

1st column has a recurring value, which is an identifier of a customer.

2nd column has a unique value.

Each row has a start and end date.

 

Now the first step i want to do is rank the rows to the 1st column.

So if i have 10 rows, with 2 unique customer, where the 1st customer has 3 rows, and the 2nd customer has 7 rows, i want the following output:

 

CustomerIdWorkorderIDRank
111
122
133
2121
2132
2143
2154
2165
2176
2187

 

Now ofcourse, this is logical, but in terms of coding it is not. Because i never defined a rank by criteria. So if this is the result it is pure luck.

Now how would i write this in DAX? In OBIEE i would write something like: RANK(WorkorderID BY CustomerID)

 

I have tried using RankX, but i'm not getting it. So i'm not exactly looking for a answer to my question, i'm more looking for an explanation how to get to that answer.

 

So the last time i asked this question, i got a full answer. But now i'm hoping someone can explain how i get to that answer.

 

This is what i eventually want (per customer id):

 

CustomerIdWorkorderIDRankStart dateEnd dateWithin 7 days of previous workorder? 
1111-1-20191-1-20190False, there is no previous workorder
1222-1-20196-1-20191True, within 1 day a new workorder is created
13311-1-201915-2-20191True, within 7 days of closing last workorder a new one is created

 

For this, i've gotten the following code last time. But its not functioning 100% and since i don't understand the elements i can't fix it. (also this one compares start to start, not end old vs start new)

 

Indicatorx:=VAR thisdate =
MAX ( [Start] )
RETURN
CALCULATE (
COUNT ( [Ticketnr] );
FILTER (
ALLSELECTED ( Table1 );
[UniqueID] = MAX ( [UniqueID])
&& [Start] >= thisdate - 6
&& [Start] <= MAX ( [Start] )
)
)

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    Hi, decarsul 

     

    Based on your description, I modified the table as below.

    Table:

     

    You may modified the measure as below. SUM('Table'[WorkorderID]) need to be changed as MAX('Table'[WorkorderID]) because text column can not be used in sum aggregation.

    Rank Measure = 
RANKX(
    FILTER(
       ALL('Table'),
       [CustomerId]=MAX('Table'[CustomerId])
    ),
    CALCULATE(MAX('Table'[WorkorderID])),
    ,ASC
)

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

8 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    5 years ago

    Hi, decarsul 

     

    Based on your description,  I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    You may create two measures as below..

    Rank Measure = 
RANKX(
    FILTER(
       ALL('Table'),
       [CustomerId]=MAX('Table'[CustomerId])
    ),
    CALCULATE(SUM([WorkorderID])),
    ,ASC
)

     

    IsWithin 7 Days = 
var r = [Rank Measure]
var lastenddate = 
MAXX(
    FILTER(
        ALL('Table'),
        [CustomerId]=MAX('Table'[CustomerId])&&
        [Rank Measure]=r-1
    ),
    [End Date]
)
var diff = DATEDIFF(lastenddate,MAX('Table'[Start Date]),DAY)
return
IF(
    ISBLANK(lastenddate),
    0,
    IF(
        diff>=7,
        0,1
    )
)

     

    First, you need to get the value of 'CustomerId' and use it to filter 'All' table.

    FILTER(
       ALL('Table'),
       [CustomerId]=MAX('Table'[CustomerId])
)

    Then you may use 'Rankx' function to get the rank by 'WorkorderID' in the context

     of the above filtered table.

    RANKX(
    FILTER(
       ALL('Table'),
       [CustomerId]=MAX('Table'[CustomerId])
    ),
    CALCULATE(SUM([WorkorderID])),
    ,ASC
)

     

    Result:

     

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • decarsul's avatar
      decarsul
      Helper V
      5 years ago

      Hi Allan,

       

      The results are exactly what i'm looking for, thanks for that.

      For me to get this right. 

       

      The Filter in this case acts as my group by statement?

      As in, with the filter i select my target group, and by applying that filter in the rankx to ALL(Table) i basically tell it to only do the ranking on each unique customer id that it can find in all columns and rows of the table?

       

      Now how does the customerid= max(customerid) define the uniqueness? i don't understand that.

       

      Also, What if the unique id is not INT but instead varchar. This won't work with a count 😞

      • v-alq-msft's avatar
        v-alq-msft
        Community Support
        5 years ago

        Hi, decarsul 

         

        We limit the scope of the rank by applying that filter in the rankx to ALL(Table). 'customerid= max(customerid)' will filter the rows where customerid equals to the cutomerid in current context in ALL(Table),

         

        If the customerid isn't a number but a text. It will have no effect on the result.

         

        Best Regards

        Allan

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

         

  • craigbarrien1's avatar
    craigbarrien1
    New Member
    3 years ago

    This was great and helped me a lot for my scenario. However, when I try to use this in a bar chart to show how much the price is for each of my categories, the values are showing for the overall total not just the ones whose dynamic ranking is 1. Even when I try to use the measure as a filter it shows blank. I know it's been a few years but any help on this would be great.