Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have some binned data that I am ranking by the count. I have created a measure that ranks the count by descending. The table looks like this:
The code for the measure is as follows:
clc_Rank = RANKX(ALL('vmFNC_DATA_DAILY_RTH()'[Volume (bins)]),CALCULATE(COUNTA([Volume])))
When I try and use the above code in a column, PowerBI complains of a circular dependancy.
Is it possible to have a calculated column to perform the rank rather than the measure, and if so, what would the code look like?
thank you,
Solved! Go to Solution.
Hi @vmonkey,
For your scenario, you can create the calculated column using the formula below.
Rank = RANKX ( 'vmFNC_DATA_DAILY_RTH()', CALCULATE ( COUNTA ( 'vmFNC_DATA_DAILY_RTH()'[Volume (bins)] ), ALLEXCEPT ( 'vmFNC_DATA_DAILY_RTH()', 'vmFNC_DATA_DAILY_RTH()'[Volume (bins)] ) ), , DESC, DENSE )
If you want to use measure to get the rank, please try the following formula, and check if it works fine.
clc_Rank = RANKX ( 'vmFNC_DATA_DAILY_RTH()', CALCULATE ( COUNTA ( 'vmFNC_DATA_DAILY_RTH()'[Volume (bins)] ), ALLEXCEPT ( 'vmFNC_DATA_DAILY_RTH()', 'vmFNC_DATA_DAILY_RTH()'[Volume (bins)] ) ), , DESC )
Please feel free to ask if you have other issue.
Best Regards,
Angelia
Hi @vmonkey,
For your scenario, you can create the calculated column using the formula below.
Rank = RANKX ( 'vmFNC_DATA_DAILY_RTH()', CALCULATE ( COUNTA ( 'vmFNC_DATA_DAILY_RTH()'[Volume (bins)] ), ALLEXCEPT ( 'vmFNC_DATA_DAILY_RTH()', 'vmFNC_DATA_DAILY_RTH()'[Volume (bins)] ) ), , DESC, DENSE )
If you want to use measure to get the rank, please try the following formula, and check if it works fine.
clc_Rank = RANKX ( 'vmFNC_DATA_DAILY_RTH()', CALCULATE ( COUNTA ( 'vmFNC_DATA_DAILY_RTH()'[Volume (bins)] ), ALLEXCEPT ( 'vmFNC_DATA_DAILY_RTH()', 'vmFNC_DATA_DAILY_RTH()'[Volume (bins)] ) ), , DESC )
Please feel free to ask if you have other issue.
Best Regards,
Angelia
Thank you for your help v-huizhn-msft,
With the new column code provided in your post, the resulting table looks as follows:
The current code that I am using for the measure (as seen in my first post) is working perfectly. I am trying to get it to work as a column calculated field rather than a measure.
Hi @vmonkey,
In the resource table, the rank works fine, right? If it is, please "don't summarize" when you select the calculated column in table visual.
Best Regards,
Angelia
Ahh ... I see my problem ... thank you Angela. I was attempting to use slicers on the data which was giving weird results. I was not aware that they had no effect on the column calculation. Makes sense.
My need for the column calculation was to help create a running total measure based on the order of [Count of Volume] as what I was doing was not working with the [clc_Rank] measure. I'll post that question in a different thread.
thank you!
User | Count |
---|---|
90 | |
73 | |
67 | |
63 | |
55 |
User | Count |
---|---|
99 | |
88 | |
73 | |
60 | |
58 |