Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have the following columns,
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
Solved! Go to 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
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
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
Hi @BhaveshPatel,
As my model contains some confidential data I cannot share it with anyone.
Thanks,
Rashmita Reddy.
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]))"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |