Sort Trial.pbix (1.5 MB)
Hi everyone,
I’m trying to sort this data (exactly how it is with one small change) I’m unable to get the line w’ “Cost Item” to live at the bottom of the report. I tried to sort by Scope of work Id (Which is sequential) - Cost item does not have a scope of work id and needs to be at the bottom.
Any ideas?
@cms418 ,
Is it oversimplifying just to sort Cost Item in the reverse order as such?:
You can also sort on multiple other criteria first and then sort Cost Item in this order by holding down the SHIFT key when you sort the table.
The other thing you can do is sort by multiple columns in PQ, and then when you’ve got everything the way you like it, add an index column, so that you can return it to that original order w/ one click just by sorting the index.
Yes, oversimplifying. The project costs come in as per the layout… Except the blank cost item needs to be at the bottom. I also tried using an index column through power query, same thing… Kept placing the blank row at the top!
@cms,
Can you please provide your work in progress PBIX and underlying data file, or a masked equivalent so that I can test out a number of approaches. Thanks.
– Brian
Hi @cms418
Here are some videos that may help you masking sensitive data and create datasets and data models representative of your problem:
Cost File-Dummy Data.xlsx (4.5 MB)
Hey Brian - here is the data file that goes with the PBIX above. Thanks for your support.
@cms418 ,
Let me ask you this, are you sure that the records where cost item equals blank are actually valid records, and are not artifacts of referential integrity problems with your data model? When I look at that data model, I see a myriad of potential problems – fact tables connected with each other, dimension tables connected together, a many to many relationship - lots of violations of modeling best practices. When I tried to simplify the problem by pareing it down to the minimal number of relevant tables, the blank records disappeared.
So I’m thinking that the problems we’re having sorting the table are indicative of larger data model problems. Thoughts?
– Brian
Data model is solid and all measures check out. We shouldn’t have to go beyond lem entry for this, so unsure what’s up.
@cms,
Try this:
Add a 1-to-N index field on that same table.
Close and apply.
In the field list, select Cost Item, the go to the top menu and select Sort by column, and choose the index column you created in Step 2 above.
I think that should do the trick for you.
Hi @cms418.
I agree a data modelling update would be my first thought; it may be sufficient for the testing you’ve done to date, but DAX can return some unpredictable results from a model with bi-directional relationships and relationship between fact tables.
That being said, if the only issue you have is that you want the (blank) [Sort Order] sorted last, you could simply add a calculated column to the [Budget] table to set blanks to an arbitrarily large number, like.
Sort Order 2 =
IF(
ISBLANK( Budget[Sort Table.Sort Order] ),
99999,
Budget[Sort Table.Sort Order]
)
, then sort the [Cost Item] colum by the new [Sort Order 2] column.
I’m sure I’m missing something, as I’d expect you’ve already tried and discarded this, but thought I’d post it anyway.
Good luck.
Greg
This worked - I did try something similar and couldn’t quite get it. I’ll change up the model, but when I do, will be back with questions I’m sure! haha, this community is great - thanks so much!
Thanks Brian!
@Greg ,
Nice! I originally tried a similar approach with LEN( Budget[Cost Item]), but ran into a circular reference problem, and then went down a rabbit hole of trying to break the lineage to solve the circular reference. Yours is a much simpler, more elegant solution.
– Brian
