Rank values based on Month filter

[RashmitaR]

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

[v-shex-msft · Accepted solution]

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

[RashmitaR]

Hi @v-shex-msft thank you so much that helped.

[BhaveshPatel]

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]

Hi @BhaveshPatel,

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

Thanks,

Rashmita Reddy.

[BhaveshPatel]

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]))"