Create a timeline of count of values within a multi-select field?

[PKCrash]

Hey team,

This is my first post here so please be gentle :). Tough one here, any help would be appreciated.

Within Jira, we have a mutli-select field called Status Check. I am using the History table's data to try and create a timeline showing each day over the course of a month, with the values being the amount of Status Checks that existed across all tickets on each day. For example if on 9/30/2022, two tickets had Status checks, and one of those tickets had 3 values in the field selected and another had 1 value selected, I would like the displayed value shown on the timeline for that day to be 4.

In the case of there being multiple rows with the same Key on one day, it would be best to use the LAST one of that day (since automations may be clearing out Checks that have been corrected by users)

Here is a link to an empty PBIX file as well as an excel with a snippet of the data. If anyone could point me in the right direction that would be incredible.

One last thing, I've also posted this to the Tableau community as they have been able to help me in the past. While I need to use Power BI for this, I hope the Tableau post could be helpful.

Thanks!

[Anonymous]

Hi @PKCrash ,

I'm not clear about the following sentences which you refer in the post, could you please provide some special examples to illustrate it? And what's the calculation logic? Do you want to get the count of key during the selected dates? By the way, the key means the ticket? Thank you.

---

For example if on 9/30/2022, two tickets had Status checks, and one of those tickets had 3 values in the field selected and another had 1 value selected, I would like the displayed value shown on the timeline for that day to be 4.

---

Best Regards

[PKCrash]

Hi @Anonymous 

Thanks much for getting back to me. Yes, Key means ticket. The final result I want is a count of the last value in the Checks field across all Keys, on each day. This count should be distinct in that it only counts a Key once, and again, the row counted should be the last one of that day.

In my example:

Row #	Key	Checks	Time
1	Key-1	3	9/30/2022 11:00 AM
2	Key-1	1,3,4	9/30/2022 3:00 PM
3	Key-2	2	9/30/2022 4:00 PM

Since we are only counting the LAST value, we would only count row 2 for Key-1 and ignore row 1. Those 3 vales (1,3,4) plus Key-2's 1 Check (2) equals a total of 4 for 9/30/2022.

Does that make sense?

Let me know,

Thanks!

[Anonymous · Accepted solution]

Hi  @PKCrash ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create two measures as below:

Measure = 
VAR _selkey =
    SELECTEDVALUE ( 'Table'[Key] )
VAR _selchecks =
    SELECTEDVALUE ( 'Table'[Checks] )
VAR _seltime =
    SELECTEDVALUE ( 'Table'[Time] )
VAR _lasttime =
    CALCULATE (
        MAX ( 'Table'[Time] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Key] = _selkey )
    )
VAR _count =
    PATHLENGTH ( SUBSTITUTE ( _selchecks, ",", "|" ) )
RETURN
    IF ( _seltime = _lasttime, _count, BLANK () )

Count of the last value = 
IF (
    ISBLANK ( [Measure] ) && ISINSCOPE ( 'Table'[Key] ),
    BLANK (),
    SUMX ( GROUPBY ( 'Table', 'Table'[Key], 'Table'[Checks] ), [Measure] )
)

Best Regards