Custom Sort X Axis to Show Blank Values at the End

[newpbiuser01]

Hello,

I have a bar chart with the dates being displayed on the X axis. The date column is also blank for certain records and is currently being sorted to always show up at the beginning of the axis (or the end if I sort in the descending order). To create a custom sort ID, I did the following:

1. Created a measure to get the max of the date, and another to get the minimum of the date

2. Created a calculated date table with the min and the max values from the measures in 1 and created a custom column using the Rankx function to assign a rank to the dates. 

3. Created a dummy table with a blank row for the date and a dummy index.

4. Combined the two tables in 2 & 3 using the union function and created my sort ID saying - if(isblank([date]),maxrank+1, rank)

5. Use the lookupvalue to get the rank for each date in the primary data table. 

Once this is generated, I figured I could now just go to the date column and go the "Sort by column" and sort by my rank column. However, I get an error saying  "A circular dependency was detected". How do I get around this error and sort my column so the blanks always show up at the end of the X axis?  Thank you so much for all your help. 

[v-yalanwu-msft]

Hi, @newpbiuser01 ;

You could create a table as follow:

DateTable = UNION(  ADDCOLUMNS( SUMMARIZE( FILTER('Table',[Date]<>BLANK()),[Date]),"sortid",RANKX('Table',[Date])),
SUMMARIZE(FILTER('Table',[Date]=BLANK()),[Date],"sortid",0))

Then create a column in table.

month = IF([Date]=BLANK(),BLANK(), FORMAT([Date],"mmm"))

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[newpbiuser01]

Hi @v-yalanwu-msft ,

Thank you! This works great when the data is all in the same year, but it doesn't when the year is different. I tried creating a year column similarly to the month to see if that would help. 😞 

[v-yalanwu-msft · Accepted solution]

Hi, @newpbiuser01 ;

May be we need use power query : in power query create a new column .

1.group by date.

2.sort by date.

3.add index column.

4.add conditional column and delete index column.

45.expand spend column

then apply it into desktop.

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[newpbiuser01]

Thank you @v-yalanwu-msft! This worked perfectly. I really appreciate your help! 

[v-yalanwu-msft]

Hi, @newpbiuser01 ;

A circular dependency is detected whenever two objects reference each other, in such a way that Power BI cannot process the objects.

Avoiding circular dependency errors in DAX - SQLBI

because i can't Reproduce your data. please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

Solved: How to upload PBI in Community - Microsoft Power BI Community

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[newpbiuser01]

Hi @v-yalanwu-msft ,

My spend data looks like the following: 

Date	Spend
2012-10-30	188
2012-10-11	100
2012-11-13	162
2012-10-27	136
2012-11-08	161
2012-10-31	138
2012-11-17	196
2012-10-21	124
2012-10-13	195
	115
	176
	162
	111
	159

When I try to plot it on a bar chart, I get the blanks at the beginning, followed by October and then November. 

To do that, I created two measures: 

MinDate = Min([Date])

MaxDate = max([Date])

and then created a calculated table using these dates: 

DateTable = CALENDAR([MinDate],[MaxDate])

I then created a calculated column in this DateTable to rank the dates in the date column - 

IndexID = RANKX('DateTable',[Date]). 

To add the rank for blank dates, I created another data table using the Enter Data functionality, and the table looks like this:

I then merge the two tables: 

SortTable = Union('DateTable','Dummy Table') 

Finally, I add a calculated SortID column which assigns 0 to the sortID for date = blank, otherwise uses the ranked index: 

 Now I have a table that I can use to sort the date column, and am hoping to sort the date field in my original spend table with this SortID. So I created a relationship between the SortTable and the Spend table using the Date column and try to sort the Date by the SortID, but I get the following error: 

How can I sort my date column in a way so the blank dates get sorted at the end without a circular dependency? I'd really appreciate any help! Thank you!