Sorting Summarized table

[RonaldvdH]

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

[lbendlin]

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-...
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/1447...

[RonaldvdH]

@lbendlin 

Based 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]

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[RonaldvdH]

@lbendlin i'll try to provide sample data

table 1

Street	Begin	End
Street A	01-09-23	08-09-23
Street A	02-09-23	06-09-23
Street B	05-09-23	12-09-23
Street B	12-09-23	28-09-23
Street C	14-09-23	16-09-23
Street C	03-09-23	04-09-23
Street D	04-09-23	05-09-23
Street D	11-09-23	18-09-23
Street E

Table 2 (using Summarize based on Table 1 and Street and MINX and MAXX for begin/end date)

Street	Begin	End	SortOrder
Street A	01-09-23	08-09-23	1
Street B	05-09-23	28-09-23	4
Street C	03-09-23	05-09-23	2
Street D	04-09-23	18-09-23	3
Street E			5

There is a relationship between the column Street in Table 1 and Street in Table 2 (many to one)

Then I wanted to sort the column Street (in Table 2) based on begin (chronologically) but that didn't work

Everything works and the SortOrder works fine but when I try to use the Sort By Column option in PowerBi desktop it returned the Circuler dependency error

[lbendlin]

What should happen if multiple streets have the same Begin date?

[RonaldvdH]

@lbendlin then it should look at the date in End and if both are the same then just sort alphabetically 

[lbendlin]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi4pSk0tUXBU0lEyMNQ1sNQ1MgYxLaDMWB1UJUYIJWaYSpxA4qZwJYZG2JUYIkwxwmKRM0iJCcIULBaBlBgYI9xigqnEBVkcyVloSgwRnjbE4hZXoLgCGMfGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Street = _t, Begin = _t, End = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Street", type text}, {"Begin", type date}, {"End", type date}},"nl"),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Street"}, {{"Begin", each List.Min([Begin]), type nullable text}, {"End", each List.Max([End]), type nullable text}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Begin", Order.Ascending}, {"End", Order.Ascending}, {"Street", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "SortOrder", 1, 1, Int64.Type)
in
    #"Added Index"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

Note:  you haven't indicated how to sort nulls. Solved: Sort text by ascending and make nulls last - Microsoft Fabric Community

[RonaldvdH]

@lbendlin I'm sure I did something wrong 🙂
Ive chosen a new query, pasted your code in the advanced editor and saved the whole thing.

But...nothing happens.

Somehow/somewhere I need to use that query in the visual or something right ?

[lbendlin]

yes the query will appear as a new table in Power BI, and you can add its columns to a visual as needed.

[RonaldvdH]

@lbendlin  yes and no because it does appear as a table but it's not linked to the right source. It's linked to your testfile i'm guessing.

The table in my rapport it has to link with is the table Straten, so how do I do that ?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi4pSk0tUXBU0lEyMNQ1sNQ1MgYxLaDMWB1UJUYIJWaYSpxA4qZwJYZG2JUYIkwxwmKRM0iJCcIULBaBlBgYI9xigqnEBVkcyVloSgwRnjbE4hZXoLgCGMfGAgA=", BinaryEncoding.Base64), 
    Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Street = _t, Begin = _t, End = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Adres straat", type text}, {"Begin", type date}, {"Eind", type date}},"nl"),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Adres straat"}, {{"Begin", each List.Min([Begin]), type nullable text}, {"Eind", each List.Max([End]), type nullable text}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Begin", Order.Ascending}, {"Eind", Order.Ascending}, {"Adres straat", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "SortOrder", 1, 1, Int64.Type)
in
    #"Added Index"

I've changed the column names to the exact column names in my rapport but the source has to be altered to my table Straten 

[lbendlin]

Change the Source bit to point to your own source table.

[RonaldvdH]

@lbendlin and how do I do that ? 

I have 1 original table and 1 summarized new table based ont the orginal table.

That means the query should be linked to the new summarized table

[lbendlin]

let

   Source=#"New Summarized Table",

...

[RonaldvdH]

@lbendlin I wish it were that simple to me but i'm still not getting which part I need to change so can you hold my hand 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi4pSk0tUXBU0lEyMNQ1sNQ1MgYxLaDMWB1UJUYIJWaYSpxA4qZwJYZG2JUYIkwxwmKRM0iJCcIULBaBlBgYI9xigqnEBVkcyVloSgwRnjbE4hZXoLgCGMfGAgA=", BinaryEncoding.Base64), 
    Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Street = _t, Begin = _t, End = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Adres straat", type text}, {"Begin", type date}, {"Eind", type date}},"nl"),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Adres straat"}, {{"Begin", each List.Min([Begin]), type nullable text}, {"Eind", each List.Max([End]), type nullable text}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Begin", Order.Ascending}, {"Eind", Order.Ascending}, {"Adres straat", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "SortOrder", 1, 1, Int64.Type)
in
    #"Added Index"

Which part do I need to remove and where do I reference the summarized table Streets

In het Advancend editor I can only see the original tabel and this query and NOT the summarized table obviously but If you could explain it to my if I was a 5 year old it would help  

[lbendlin]

Something like this

let
    Source = #"New Summarized Table",
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Adres straat", type text}, {"Begin", type date}, {"Eind", type date}},"nl"),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Adres straat"}, {{"Begin", each List.Min([Begin]), type nullable text}, {"Eind", each List.Max([End]), type nullable text}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Begin", Order.Ascending}, {"Eind", Order.Ascending}, {"Adres straat", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "SortOrder", 1, 1, Int64.Type)
in
    #"Added Index"

[RonaldvdH]

@lbendlin blanks can be sorted alphabetically at the bottom of the sortingorder. I'll try your solution in the morning and get back to you, thanks for your time and (possible) solution 😉