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.
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!
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 |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |