Forum Discussion

JackWren's avatar
JackWren
Helper II
4 years ago
Solved

Rankx & Inscope while excluding certain values

Hi, 

 

I'm currently trying to rank document's pages by the number of views they have. 

I've successfully used rankx with inscope. However, I'm trying to remove the pages that are contain the words intro, home etc. from the ranking.

I can't seem to figure out how to do it. Do you have any ideas? 

 

Measure : 

if(
isinscope('Page Info'[PageName]),
rankx(ALLSELECTED('Page Info'[PageName]),calculate(sum('Page Info'[Total page Views])),,DESC,Dense)
)
 
Expected results:
ReportTotal Page ViewsTop Page - Views
Report A  
Report A - Home45(Don't want to count this one) 
Report A - Page 2 301
Report A - Page 3252
Report A - Page 4 103
Report B  
Report B - Intro100(Don't want to count this one either) 
Report B - Page 2 801
Report B - Page 3652
Report B - Page 4 303
 
Thank you 
 
Jack
DAX
Help appreciated
isinscope
RANKX
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi JackWren ,

     

    Please try this measure.

    Top Page - Views = 
IF (
    ISINSCOPE ( 'Page Info'[PageName] )
        && MID ( MAX ( 'Page Info'[PageName] ), 12, 4 ) = "Page",
    RANKX (
        ALLSELECTED('Page Info'[PageName]),
        CALCULATE( SUM ( 'Page Info'[Total page Views] )),
        ,
        DESC,Dense
    ) - 1
)

    Best Regards,
    Community Support Team_Gao

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems with it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly -- How to provide sample data

4 Replies

  • Whitewater100's avatar
    Whitewater100
    Solution Sage
    4 years ago

    Hi:

    Can you duplicate or reference this table in Power Query and Filter out there? Then apply your ranking measure on new table? Because it seems like a number of pages to get rid of I suggest PQ.

     

    If some index could be asigned to these types of pages upfront that could help down the road.

     

    Just my thoughts on this. Someone else may have a unqiue way, which is easier if it's just one type of word you are trying to avoid.

     

    Hope this helps..

    • JackWren's avatar
      JackWren
      Helper II
      4 years ago

      Sorry for the delay, you might be right. I'll try doing it directly in the table instead of using Dax. 

       

      Thank you 🙂 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi JackWren ,

     

    Please try this measure.

    Top Page - Views = 
IF (
    ISINSCOPE ( 'Page Info'[PageName] )
        && MID ( MAX ( 'Page Info'[PageName] ), 12, 4 ) = "Page",
    RANKX (
        ALLSELECTED('Page Info'[PageName]),
        CALCULATE( SUM ( 'Page Info'[Total page Views] )),
        ,
        DESC,Dense
    ) - 1
)

    Best Regards,
    Community Support Team_Gao

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems with it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly -- How to provide sample data

    • JackWren's avatar
      JackWren
      Helper II
      4 years ago

      Thank you ! I'll tweak it a little but I think we are headed in the right direction