Sorting data for multi row card visual

[Anonymous]

Hello everyone. I'm having some trouble displaying data on a multi row card.

My dataset is an SharePoint document that contains 5 columns, one of which is a calculated by dax.

Disclaimer: Scheduled column is in "dd/mm/yyy" format, and On Hold column is = Datediff(Today(),[Scheduled],Day)

My goal is to create a multi row visual that displays the next activity to begin of a certain Model.

I've created the visual using the fields below:

With Next Activity being the measure = Calculate(Min([Activity]),[Days On Hold]=Min([Days On Hold])) 

The thing is the multi row card is displaying the Activity in Alphabetical Order, which is incorrect. The rule is if more than one activity is scheduled for the same day we must sort the dataset by the Order column to know which activity is the next one. Below is an example of the incorrect visual (using the dataset sample I made at the start of this post):

And this is how it is supposed to be (still using the dataset sample I made at the start of this post):

And as you can see, even if the activities are scheduled to the same day, the card still manages to display the real first Activity sorted by the Order column. Edit: I've already tried "Sort By Column". Can someone help me achieve this configuration? Thanks in advance.

[mh2587]

Select the Activity Column in Data Tab and then sort by the order column 

[Anonymous]

Sorry, I didn't mention on the Post.

But I've already sorted Activity Column by Order Column as you suggested.

[mh2587]

Then try visual option to sort by order column

[Anonymous]

I'm not using Order Column at the visual and I can't seem to find a way that let me Sort the  activities without applying Order to the visual. Besides, anytime I try to add said column to the visual it shows all of the Activities instead of only the next one.

[Anonymous · Accepted solution]

Hi @Anonymous ,

I think you can try If function and add some filter in your code to achieve your goal.

My Sample:

We can see in my sample 1s with same "Days On Hold" should return "Refresh Data" for the smallest Order .

2s should return "Refresh Data" for the smallest "Days On Hold".

Measure:

Next Activity =
VAR _COUNT_SCHEDULE_AFTER_TODAY =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Scheduled] ),
        FILTER ( 'Table', 'Table'[Scheduled] > TODAY () )
    )
VAR _ACTIVITY_MAX_ORDER =
    CALCULATE (
        MAX ( 'Table'[Activity] ),
        'Table'[Days On Hold] > 0
            && 'Table'[Order] = MIN ( 'Table'[Order] )
    )
VAR _NEXT_ACTIVITY =
    CALCULATE (
        MIN ( [Activity] ),
        'Table'[Days On Hold] > 0
            && 'Table'[Days On Hold] = MIN ( 'Table'[Days On Hold] )
    )
RETURN
    IF ( _COUNT_SCHEDULE_AFTER_TODAY = 1, _ACTIVITY_MAX_ORDER, _NEXT_ACTIVITY )

Result 

 

Best Regards,
Rico Zhou

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