Forum Discussion
Sorting data for multi row card visual
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.
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
