Forum Discussion
Sorting Summarized table
Hey Guys,
I've got a summarized table based on unique streetnames and then a couple of date columns (begin and end)
Those date columns are based on another table using MINX and MAXX
I want to sort them based on first date at the top and last date at the bottom (no date is further at the bottom)
Ive tried a rankx formula but when I use it PowerBi return a circular dependency error.
Is there any way around that or another option because my visual is very messy now and I want it cleaned up
Sortorder =
IF(
NOT(ISBLANK(Straten[Start Civiel])),
RANKX(
Filter(ALLSELECTED(Straten[Start Civiel]),NOT(ISBLANK(Straten[Start Civiel]))),
Straten[Start Civiel],
,ASC
)
)
16 Replies
Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Based on anoter table ive created a new table using Summarize based on the streetname.
Then I've used the minx and maxx formula to search the other table on the first and last date (start date / end date)
Start Civiel = MINX( Filter( taken,Straten[adres straat]=Taken[adres straat]), Taken[begin] )Eind Civiel = MAXX( Filter( taken,Straten[adres straat]=Taken[adres straat]), Taken[eind] )Some streets already have a plandate and some don't so the formula returns a BLANK()
But in my visual (custom Gantt Chart) i can't seem to sort the streets chronologically.
Therefore i was searching a way to do so and then I made a new column SortOrder using the RANKX formula.
Sortorder = IF( NOT(ISBLANK(Straten[Start Civiel])), RANKX( Filter(ALLSELECTED(Straten[Start Civiel]),NOT(ISBLANK(Straten[Start Civiel]))), Straten[Start Civiel], ,ASC ) )But when selecting the streetname in the visual and selecting (in the ribbon) sort by column the Sort Order it returns this circuler error between Straten[Street], Straten [SortOrder], Straten[Street]
Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.
