Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

RANKX with ALLSELECTED

Need some help.

 

Here's the dataset.

 

I need this to rank the by dates within in project (desc). I've gotten this far.

 

The problem I'm running into is that I don't know where to drop an ALLSELECTED so that this doesn't happen when filtered. These should both now return a rank of 1 within the current filter context.

 

 

 

 

 

 

Need Help
  • OwenAuger's avatar
    OwenAuger
    4 years ago

    Hi Anonymous 

    Glad we've solved the initial problem 🙂

     

    I take it that the Count_ measure is one you have already tried, referencing the RANK measure?

    To get it working as intended, we just have to make sure we filter on the correct Dates per Project.

     

    See attached PBIX for example.

     

    I would suggest something like either of these:

    Count_ = 
SUMX (
    VALUES ( Sheet1[Project #] ),
    VAR DateFilter =
        CALCULATETABLE (
            FILTER (
                ALLSELECTED ( Sheet1[Date] ),
                [RANK] = 1
            )
        )
    RETURN
    CALCULATE (
        SUM ( Sheet1[Count] ),
        DateFilter
    )
)

Count_ v2 = 
VAR 
ProjectDateFilter =
    GENERATE (
        VALUES ( Sheet1[Project #] ),
        CALCULATETABLE (
            FILTER (
                ALLSELECTED ( Sheet1[Date] ),
                [RANK] = 1
            )
        )
    )
RETURN
    CALCULATE (
        SUM ( Sheet1[Count] ),
        ProjectDateFilter
    )

     

    Regards,

    Owen

4 Replies

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    4 years ago

    Hi Anonymous 

    Try this:

    RANK = 
RANKX (
    CALCULATETABLE (
        VALUES ( Sheet1[Date] ),
        ALLSELECTED (),
        VALUES ( Sheet1[Project #] )
    ),
    Sheet1[Date],
    MAX ( Sheet1[Date] )
)

    First argument of RANKX is a table containing distinct dates in "overall" filter context of the visual, but retaining the Project # filter.

     

    Regards,

    Owen

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks for this Owen. This is perfect. There is another lay to the complxity however. 

       

      If I throw this into a bar chart where I'm just wanting the total count for the lines that rank as 1, it gives me the right answer of 120.

       

      However, if I throw in the additional filter context I've category, I then get a total number higher than 120. But I still just want it to sum to 120. 

       

      I think I understand that the additional filter context is affecting the way the Rank works, but I just can't figure out how to get around it.

       

      Any thoughts?

       

       

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    4 years ago

    Hi Anonymous 

    Glad we've solved the initial problem 🙂

     

    I take it that the Count_ measure is one you have already tried, referencing the RANK measure?

    To get it working as intended, we just have to make sure we filter on the correct Dates per Project.

     

    See attached PBIX for example.

     

    I would suggest something like either of these:

    Count_ = 
SUMX (
    VALUES ( Sheet1[Project #] ),
    VAR DateFilter =
        CALCULATETABLE (
            FILTER (
                ALLSELECTED ( Sheet1[Date] ),
                [RANK] = 1
            )
        )
    RETURN
    CALCULATE (
        SUM ( Sheet1[Count] ),
        DateFilter
    )
)

Count_ v2 = 
VAR 
ProjectDateFilter =
    GENERATE (
        VALUES ( Sheet1[Project #] ),
        CALCULATETABLE (
            FILTER (
                ALLSELECTED ( Sheet1[Date] ),
                [RANK] = 1
            )
        )
    )
RETURN
    CALCULATE (
        SUM ( Sheet1[Count] ),
        ProjectDateFilter
    )

     

    Regards,

    Owen

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks for this Owen. I've spent a few hours scratching my head over this one. It will be fun reverse engineering this formula. 

      Legend!