Forum Discussion

RashmitaR's avatar
RashmitaR
Helper IV
9 years ago
Solved

Rank values based on Month filter

Hi,

I have the following columns,

  1. child library
  2. no. of views
  3. month
  4. month-year

I wanted to find the rank of child librarues based on the number of views ,the rank must change according to the month-year selected in the filter.Eg, if all the month-year are selected then the collective rank of the chid library must be shown .If i choose sept-2016 the according to the number of views in sept-2016 the rank of the libraries must be shown.

 

 

I have used below calculation to calculate the sum of views according to chid libraries and month-year

 

View Count = IF([File Accessed]="File Accesses",1,0)

 

Views_month-year = CALCULATE(COUNT(F_KNO_FILEACCESSED_AUDITSPLIT[View Count]),ALL(F_KNO_FILEACCESSED_AUDITSPLIT[Month-Year].[Month]),ALLEXCEPT(F_KNO_FILEACCESSED_AUDITSPLIT,F_KNO_FILEACCESSED_AUDITSPLIT[Child_Library]))

 

and the below calculation to find the rank

 

Views_Rank_month-year = RANKX(F_KNO_FILEACCESSED_AUDITSPLIT,F_KNO_FILEACCESSED_AUDITSPLIT[Views_month-year],,DESC,Dense)

 

 

As you can see I am missing out rank 30,31 etc

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi RashmitaR,

     

    Based on test, it works on my side, below is the test sample:

     

     

    Merge the records:

     

    Table = DISTINCT( SELECTCOLUMNS(Test,"Child",Test[child_library],"Month",FORMAT( Test[month-year],"mmmm-yyyy"),"View Count",SUMX(FILTER(ALLSELECTED(Test),Test[month-year].[Month]=EARLIER(Test[month-year].[Month])&& Test[child_library]=EARLIER(Test[child_library])),Test[no. of views])))

     

     

    Rank measure:

    Rank = RANKX(ALLSELECTED('Table'),CALCULATE(SUM('Table'[View Count])),,DESC,Dense)

     

    Create a table visual and set columns to don’t summarize:

     

     

    I agree with BhaveshPatel’s point, if ‘Views_Rank_month-year’ is a calculated column instead of a measure, the rank values is fixed when you display in the table, so after you set the visual level filter, it just filter records for those rank values.  If it’s a measure, your issue may related to calculate columns, I would suggest you insert a new table, drag [Child_Library], [file Accessed], ‘View Count’, ‘Views_month-year’ and ‘Views_Rank_month-year’ to the table to find whether the missed records can display. Also please enable ‘Show items with no data’ for each field in the table.

     

     

     

    Regards,

    Xiaoxin Sheng

5 Replies

  • BhaveshPatel's avatar
    BhaveshPatel
    Super User
    9 years ago

    Hi There,

     

    There are no. of reasons for this behaviour as RANKX is bit tricky to handle. Can you please share sample file to get exact solution.

     This calculated column seems culprit for this behaviour.

     

    "Views_month-year = CALCULATE(COUNT(F_KNO_FILEACCESSED_AUDITSPLIT[View Count]),ALL(F_KNO_FILEACCESSED_AUDITSPLIT[Month-Year].[Month]),ALLEXCEPT(F_KNO_FILEACCESSED_AUDITSPLIT,F_KNO_FILEACCESSED_AUDITSPLIT[Child_Library]))"

     

     

    Thanks & Regards,

    Bhavesh

    • RashmitaR's avatar
      RashmitaR
      Helper IV
      9 years ago

      Hi BhaveshPatel,

       

      As my model contains some confidential data I cannot share it with anyone.

       

      Thanks,

      Rashmita Reddy.

      • BhaveshPatel's avatar
        BhaveshPatel
        Super User
        9 years ago

        Ok Not a Problem. 

         

        Can you please try to explain what do you trying to calculate in this column.

         

        "Views_month-year = CALCULATE(COUNT(F_KNO_FILEACCESSED_AUDITSPLIT[View Count]),ALL(F_KNO_FILEACCESSED_AUDITSPLIT[Month-Year].[Month]),ALLEXCEPT(F_KNO_FILEACCESSED_AUDITSPLIT,F_KNO_FILEACCESSED_AUDITSPLIT[Child_Library]))"

         

         

         

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi RashmitaR,

     

    Based on test, it works on my side, below is the test sample:

     

     

    Merge the records:

     

    Table = DISTINCT( SELECTCOLUMNS(Test,"Child",Test[child_library],"Month",FORMAT( Test[month-year],"mmmm-yyyy"),"View Count",SUMX(FILTER(ALLSELECTED(Test),Test[month-year].[Month]=EARLIER(Test[month-year].[Month])&& Test[child_library]=EARLIER(Test[child_library])),Test[no. of views])))

     

     

    Rank measure:

    Rank = RANKX(ALLSELECTED('Table'),CALCULATE(SUM('Table'[View Count])),,DESC,Dense)

     

    Create a table visual and set columns to don’t summarize:

     

     

    I agree with BhaveshPatel’s point, if ‘Views_Rank_month-year’ is a calculated column instead of a measure, the rank values is fixed when you display in the table, so after you set the visual level filter, it just filter records for those rank values.  If it’s a measure, your issue may related to calculate columns, I would suggest you insert a new table, drag [Child_Library], [file Accessed], ‘View Count’, ‘Views_month-year’ and ‘Views_Rank_month-year’ to the table to find whether the missed records can display. Also please enable ‘Show items with no data’ for each field in the table.

     

     

     

    Regards,

    Xiaoxin Sheng