Sorting Gallery by Multiple Columns if One is Blank

[jacktransform]

Hi all!

I have a table in Dataverse called Table that has two columns, SometimesFilled and AlwaysFilled. SometimesFilled is the more important of the two columns, but like it's name implies it only sometimes contains a value. Meanwhile, AlwaysFilled will always have a value for every record in my table Table. Think of AlwaysFilled like the primary column for this table.

I have a gallery in my app with the items set to [@Tables], and whenever possible I want the Title control (called TitleControl) of my gallery to show ThisItem.SometimesFilled. Whenever ThisItem.SometimesFilled is blank I want TitleControl to be instead ThisItem.AlwaysFilled. TitleControl is the following code:

If(

!IsBlank(ThisItem.SometimesFilled),

ThisItem.SometimesFilled,

ThisItem.AlwaysFilled

)

I'm having problems sorting the gallery alphanumerically by what is essentially in TitleControl. I set the Items property for the gallery to the following code, but end up with a delegation warning:

Sort([@Tables], "TitleControl", Ascending)

Has anyone had success with putting a condition in the Title control of their gallery and then sorting their gallery by what's in the Title control without getting delegation warnings? 

Thanks!

[v-bofeng-msft · Accepted solution]

Hi @jacktransform :

Could you tell me if your sort formula is like this:

Sort(
    jacktransformTests,
    If(
        !IsBlank(SometimesFilled),
        SometimesFilled,
        AlwaysFilled
    ),
    Ascending
)

if so, let me explain why you encountered this problem.

The key is that TitleControl is not a real column, it is a "virtual" column obtained by calculation.So your Sort formula can not be delegated.So far, the deletion warning cannot be eliminated.

Finally,I have two alternatives:

Solution 1:Add an automatically calculated auxiliary column in the data source

Then sort directly according to this calculated column('Auxiliary column'), the effect is the same

Sort(
    jacktransformTests,
    'Auxiliary column',
    Ascending
)

Solution 2:If your data source has less than 2000 records, you can ignore this deletion warning(Just set 'dat row limit ' to 2000).

 Best Regards,

Bof

[jacktransform]

@v-bofeng-msft 

Wow I had no idea you could even do calculated columns! I'll give that a try!

This is not related to my original questions, but I have my tables in the dataverse hooked up to a PowerBI report as well. Would you recommend creating calculated columns in the dataverse for cases of simple calculations instead of doing DAX measures/calculated columns in PowerBI? Is there any benefit to one or the other?

Thanks!