Sorting by Column Corrupting Matrix

[Anonymous]

Hi guys,

I've got a customer sort order of a variable Reorder Bucket, the columns on a few of my matrices. When I apply the sort to the column, the matrices become corrupted - only the first column will get a correct value. 

This is the simplest example of a measure used in the matrices:

Retention Buckets =

Divide(COUNTA(Orders[Clarity Order ID]),

CALCULATE(counta(Orders[Clarity Order ID]),Orders[Reorder Bucket]="T"),

Before the sort, all columns had values. 

After the sort, only column T had values.

Any idea why the sort is impacting the measure?

Thanks

Oli

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

You can refer to this blog, which explains the problem:

https://www.sqlbi.com/articles/side-effects-in-dax-of-the-sort-by-column-setting/ 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @V-lianl-msft,

I've managed to use this solution for most of my graphs but on one, I cannot get it to work. Whatever I try, it just ends up with a failed graph and the error "Can't display the visual. There's not enough memory to complete this operation."

Do you have any idea what I can try? Below is the measure that's failing when the other measure "Retention Buckets" is amended to filter on both master and sort columns.

Cumulative Retention Quarterly = CALCULATE([Retention Buckets]*AVERAGE(Orders[Total Amount]),FILTER(ALLEXCEPT(Orders,Orders[Region],Orders[Trial (month/year)],Orders[Trial quarter]),Orders[Reorder Bucket Priority]<=MAX(Orders[Reorder Bucket Priority])),Orders[Reorder Bucket Priority]>0)

Here are the new and old definitions of Retention Buckets: 

Retention Buckets (NEW) =

Divide(COUNTA(Orders[Clarity Order ID]),

CALCULATE(counta(Orders[Clarity Order ID]),Orders[Reorder Bucket]="T",Orders[Reorder Bucket Priority]=1)

Retention Buckets (OLD) =

Divide(COUNTA(Orders[Clarity Order ID]),

CALCULATE(counta(Orders[Clarity Order ID]),Orders[Reorder Bucket]="T")

Thanks

Oli

[Anonymous]

This worked, thanks again!

[Anonymous]

Thanks Liang! I think this will solve it, I will try when I get home and update here.

Oli

[parry2k]

@Anonymous share sample data wiht expected output and also screen shot of what is not working. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

Hi @parry2k,

Thanks for your reply, I will do so if Liang's advice doesn't work when I'm back at my PC. It seems to be the exact same issue though so hopefully it does.

Thanks
Oli