Hello! I am very new in this blog. I have a small extract from a larger data model where I keep tack of accounts payable invoices. The model works fine except that the slicer for the due dates is not sorting correctly. I tried to create a calculated column so I can sort the days correctly and the measure was giving an error of circular reference . This is the order I need to sort my calculated column by:
Days Idled in Open Invoice
Less than 1 Week
Greater than 7 Days!
Greater than 15 Days
Greater than 30 Days
Can this be resolve using a DAX measure instead of a calculated column. Any help would be greatly appreciated
sorting DAX.xlsx (1000.8 KB)
Hi @jazzista1967
Welcome to the forum.
Have you tried “Sort by column” because you already have a sequence number?
Hi Melissa! Good morning. That column is to sort my segmentation values ( less than 100K, greater than 250K etc). I think I need another sorting for the order of the days. Thanks for replying.
Ok so I’ve misunderstood… Can you be more specific on your requirement and expected result?
Also I must say I 've never worked with PowerPivot, can you provide a PBIX file instead?
Thanks!
Hi Melissa: If you look at the slicer ( Days iddled in open invoice) , its not sorting correctly. The sorting need to be :
Less than 1 week
Greater than 7 days
Greater than 15 days
Greater than 30 days.
Right now, the sorting is
Less than 1 week
Greater than 7 days
Greater than 30 days
Greater than 15 days
Thank you so much.
Hi Melissa, Sorry for the late reply. I have tried already using custom lists ( That was my first approach before posting this thread for help in this community ) and it did not work. I was able to sort my bins ( Less than 100K, greater than 250K etc) using a measure with no problem. But, when I tried to create the same logic inside another calculated column, I had a circular reference error. I have been able to get by, by just manually re-ordering the days and I wanted to be dynamic like the sorting of the bin amounts.
This is the formula I used I the calculated column for the segment sorting
=CALCULATE(VALUES(SegmenationTable[Segment Name]),FILTER(SegmenationTable,AND(SegmenationTable[Minimum Invoice]<[Total Outstanding Invoices],SegmenationTable[Maximun Invoice]>=[Total Outstanding Invoices])))
@jazzista1967
Working with PowerPivot for the first has been a very humbling experience…
It took me forever to find your slicer was not based on the supporting table but on the fact table.
So to make Sort by work I’ve added an [IndexDaysIddled] column using the same SWITCH statement you’ve used for the [Days iddled in open invoice] but just substituting the Category names with Index numbers.
Went back to you Report and for the Slicer and PivotTable set the sort order to Data source.
This seems to be working properly now exept for the fact that you are missing a category in the slicer because there is no “data” available for that missing group in your fact table.
Sorting PowerPivot.xlsx (1.0 MB)
Melissa: Good evening. Thank you very much for your help… I did test it in the live report and it works like a charm. It shows all categories since I updated a few minutes ago. Again thanks for being so diligent on solving my problem. Regards
Welcome to the Forum @jazzista1967, It’s great to know that you are having a great experience so far. We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!
