Forum Discussion

bo_afk's avatar
bo_afk
Post Patron
5 years ago

Using rank as filter

Hi, I have a table below which shows the number of visits to a web site for each customer based on the channel. I've also created a measure to rank the channel for each customer (using RANKX). What...
Need Help
camargos88's avatar
camargos88
Community Champion
5 years ago

bo_afk ,

You just need to filter the visual:

bo_afk's avatar
bo_afk
Post Patron
5 years ago

Hi camargos88, thanks for your response.

I did try to filter the visual but it didnt work for me. What formula did you use for Rank?

I used the following:

Rank = RANKX(ALL(Table[Channel]),[Total visits])
where [Total visits] is a measure sum(visits)
  • camargos88's avatar
    camargos88
    Community Champion
    5 years ago

    bo_afk ,

     

    Use this measure, so you don't need to filter the visual:

    _Rank Channel = 
VAR _tbl = SUMMARIZE('Table', 'Table'[Channel], 'Table'[Customer], "Rank", RANKX(FILTER(ALL('Table'), 'Table'[Customer] = EARLIER('Table'[Customer])), CALCULATE(SUM('Table'[Total visits])),, DESC))
RETURN COUNTX(FILTER(_tbl, [Rank] = 1), [_Rank])

     

    • bo_afk's avatar
      bo_afk
      Post Patron
      5 years ago

      Thanks camargos88 for your suggestion!

       

      I have another column in my data, "device type", so it's possible for channel to appear on multiple rows for each customer if they browse on several devices. How do I amend the formula to take this column into account?

      • camargos88's avatar
        camargos88
        Community Champion
        5 years ago

        bo_afk ,

         

        How do you want to show this information (visual) ?