Running total of open issues

[Surfacescan]

Hi - I am trying to find a DAX measure to achieve the following.

Each day we survey to see if people are facing an issue.  Blanks and falses are considered = no issue whereas true = an issue. 

I want to keep track of the total count (running total) of ongoing issues over time.  And I also need to keep track if a client's issue has been resolved in order to get an accurate running total over time.  I plan to use this to make a time series line chart showing the open issues over date range.

Here is what I have come up with as a table that I would like.  I need help creating the Status column and the "Open issues" columns. Perhaps the Status column is unessesary?

Key	Date	Issue	Status	Open Issues (Running Total)
A	Mon	true	unresolved	1
A	Wed	false	resolved	0
A	Fri	true	unresolved	3
B	Tues			0
B	Thurs	true	unresolved	2
B	Sat	true	unresolved	4
C	Wed	true	unresolved	1
C	Sat	true	unresolved	5
C	Sun	false	resolved	4

This is how I imagine I would visualise the above data table:

Any suggestions would be welcomed!

Thank you,

Bart Sullivan

[nandukrishnavs]

@Surfacescan 

Try this example

Key	Date	Issue
A	27 April 2020	True
A	29 April 2020	False
A	01 May 2020	True
B	28 April 2020
B	30 April 2020	True
B	02 May 2020	True
C	29 April 2020	True
C	02 May 2020	True
C	03 May 2020	False

Issue column should be Text Type and Date column should be Date Type

Open Issues = 
VAR _issue =
    TOTALYTD (
        COUNT ( 'Table'[Key] ),
        'Table'[Date],
        'Table'[Issue] = "TRUE"
    )
VAR _resolved =
    TOTALYTD (
        COUNT ( 'Table'[Key] ),
        'Table'[Date],
        'Table'[Issue] = "FALSE"
    )
VAR _openissues = _issue - _resolved
RETURN
    _openissues

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[Surfacescan]

Hi @nandukrishnavs this is 95% of the way there!

I have one issue.  Because it is a weekly survey, if a respondent does not have an issue, he/she is going to respond "False" each week - I don't want the result to go into -0.  See attached chart below.

Is there a way we can set a floor so that the count never shows less than 0 - because a count of 0 is what is desired.  If it is zero that means there are no open issues!

Thanks

[nandukrishnavs]

@Surfacescan 

Open Issues = 
VAR _issue =
    TOTALYTD (
        COUNT ( 'Table'[Key] ),
        'Table'[Date],
        'Table'[Issue] = "TRUE"
    )
VAR _resolved =
    TOTALYTD (
        COUNT ( 'Table'[Key] ),
        'Table'[Date],
        'Table'[Issue] = "FALSE"
    )
VAR _openissues = _issue - _resolved
RETURN
    if(_openissues<0,0,_openissues)

I have applied an IF statement for checking the value. 

If this is not working, please share your data tables.

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[Anonymous]

Hi there.

It's best not to use the TOTALYTD function the way it's used in @nandukrishnavs's measure. Please read this to know why:

https://www.sqlbi.com/blog/marco/2018/08/10/the-hidden-secrets-of-totalytd/

Secondly, the measure does not use a proper calendar dimension, hence it's prone to errors later on (without you even noticing it). If you create a proper calendar, this measure can be translated into this:

Open Issues = 
VAR __issue =
	calculate(
		COUNT ( 'Table'[Key] ),
		datesytd( Calendar[Date] ),
		// The field 'Table'[Issue] should
		// be logical, not text! If you are
		// going to slice by the field [Issue]
		// in the 'Table', then you need keepfilters.
		// If this field is going to be hidden, then
		// you can remove the modifier and leave only.
		// 'Table'[Issue] just like that without any comparison.
		keepfilters( 'Table'[Issue] )
	)
VAR __resolved =
	calculate(
		COUNT ( 'Table'[Key] ),
		datesytd( Calendar[Date] ),
		// Same remark as above applies.
		keepfilters( NOT( 'Table'[Issue] ) )
	)
VAR __openIssues = __issue - __resolved
RETURN
    ( __openIssues > 0) * __openIssues

When you create a model, please stick to Best Practices unless you want to suffer later and spend countless hours/days/weeks trying to figure out what the code does.

Best

D 

[Surfacescan]

Just to confirm:

The  Calendar[date] should be CALENDAR('my table'[date]) ?

And all my data is stored as text (not logical) and in 20 different columns with the equivalent of yes/no

Do you advise I change these all to true / false data types?

Thanks @Anonymous

[Anonymous]

Hi.

It all depends. First of all, you should never under any circumstances expose columns in your fact table to the user. You should always create dimensions with the attributes you want to expose and slice by them only. In the fact table only measures can be exposed. About cerating a proper calendar in the model you can watch this:

https://www.sqlbi.com/tv/time-intelligence-in-microsoft-power-bi/

If you want to know how to build correct models by employing star-schema, you can watch these:

https://www.youtube.com/watch?v=_quTwyvDfG0
https://www.youtube.com/watch?v=78d6mwR8GtA

If you have a column in your fact table that is a logical flag (not exposed to the end user, of course), it should be logical, not text. If, however, you want to present a dimension column that is logical in nature to the end user, you should make it a text column with some well chosen names of the flags. For instance, if you have a flag that says whether or not a transaction is valid and you want to slice by it, then you'd change the TRUE value into "Valid", and the other one into "Not Valid."

Please try to learn about correct data modeling.

Best
D

[Surfacescan]

Hi @Anonymous thanks - I can appreciate that I have a lot more to learn about proper data modelling and I see how valuable it is for building a solid application.

While I do plan to follow your approach when I have the proper time, I have an advanced question.

When initially I tried @nandukrishnavs solution, it worked for one column but when I added a second and third calculated column using the same DAX code (let's call these water_issues and electricity_issues) I got an error about a circular dependancies.

I guess this is realted to me perforing these functions on the raw columns instead of measures.  What do you think?  Would your updated approach resolve this?

Thanks!

[Anonymous]

No, it would not. Please read this to understand where this error comes from and how to avoid it:

https://www.sqlbi.com/articles/avoiding-circular-dependency-errors-in-dax/

Best
D

[Surfacescan]

Hello again @Anonymous.  I took some time away from this and returned and took your advice to revist the data modelling.  I have implemented your suggestion and I have some strange behaviour and thought you might be able to troubleshoot it with me:

 I am using the following data:

Key	Date	Issue
A	27 April 2020	True
A	29 April 2020	False
A	01 May 2020	True
B	28 April 2020
B	30 April 2020	True
B	02 May 2020	True
C	29 April 2020	True
C	02 May 2020	True
C	03 May 2020	False

I first create this measure:

Open Issues = 
VAR __issue =
    calculate(
        COUNT ( 'TD - Fiche EIMA - Live Data Viamo Platform'[Phone] ),
        datesytd( Calendar[Date] ),
       // The field 'Table'[Issue] should
       // be logical, not text! If you are
       // going to slice by the field [Issue]
       // in the 'Table', then you need keepfilters.
       // If this field is going to be hidden, then
       // you can remove the modifier and leave only.
       // 'Table'[Issue] just like that without any comparison.

       keepfilters( 'TD - Fiche EIMA - Live Data Viamo Platform'[Open Tent Issues] )
)

VAR __resolved =
     calculate(
         COUNT ( 'TD - Fiche EIMA - Live Data Viamo Platform'[Phone] ),
         datesytd( Calendar[Date] ),
         // Same remark as above applies.

         keepfilters( NOT( 'TD - Fiche EIMA - Live Data Viamo Platform'[Open Tent Issues] ) )
     )

VAR __openIssues = __issue - __resolved
RETURN
( __openIssues > 0) * __openIssues

Then I create a quick measure for calculating the running total (I wonder if its possible to combine this into the first measure above and reduce thias to one measure?):

Open Issues running total in Started = 
CALCULATE(
    [Open Issues],
    FILTER(
            ALLSELECTED('TD - Fiche EIMA - Live Data Viamo Platform'[Started]),
            ISONORAFTER('TD - Fiche EIMA - Live Data Viamo Platform'[Started], MAX('TD - Fiche EIMA - Live Data Viamo Platform'[Started]), DESC)
    )
)

And here is the chart I am getting:

The issue is that the running total doesnt seem to be behaving as I would expect.

For example:

• There is an issue on April 27th for Key A
• The next "toggle" (non issue) for Key A is not until April 29th
• Therefore, I would expect April 28th to still show 1 issue but instead it has decremented

I think its really close, I'm just missing something that has to do with the precision of the cumulative total perhaps and the first value for a Key?

Thanks for any thoughts you have.

•  

[Anonymous]

There are several problems with your code/approach.

1. There are missing values in the Issue column. BLANK is treated as FALSE when the column in logical. Is this what you want? If not, you have to modify the measure to take this into account.

2. I don't see the model, so I can't say anything. For instance, you don't say what's Started and why you are calculating a RT using a column in a fact table instead of a Calendar.

3. Time-intel functions work properly only on proper Calendars.

4. When a measure depends on Calendar, you have to use the Calendar when showing values and calculating time-dependent values. You either cannot use any other columns or you have to know what and why you are doing what you're doing.

5. No columns from a fact table can be exposed. They must be hidden. Slicing can only be done via dimensions. If you don't do this, sooner or later you'll create a monster, hard to understand and hard to code against.

6. Your running total (from the beginning of time) is not what it should be and it'll probably never be because the measure [Open Issues] is dependent on DATESYTD function, so it'll always return data for the latest currently visible year.

7. Which field is the Calendar joining to the fact table on?

In a word, I think (from what you've shown) many things are incorrect here.

Best
D

[Surfacescan]

---

@@darlov wrote:
There are several problems with your code/approach.

1. There are missing values in the Issue column. BLANK is treated as FALSE when the column in logical. Is this what you want? If not, you have to modify the measure to take this into account.

---

• Yes - the behaviour I am going for is that if  a survey respondant does not report an issue in their weekly submission (blank) or they confirm that there is no issue (false) then it should be the same behaviour.
• If the respondant has previously reported an open issue (true) and this week the issue was resolved (false) then the issue counter should decrement to 0 ONLY FOR THAT USER.
• If a user already has a ticket open, then the ceiling for this should  be 1.  It is not possible to increase the count past one PER USER.
• If a ticket was opened two weeks ago, it should stay open (count =1) for that user as part of the cumulative total, even if other users open and close their tickets in this time.
• Essentially I am looking for a daily total of open issues which takes into account if an inidividual still has a ticket unresolved on that reporting date.

All your other questions are super helpful and revealing how much I still need to learn about PowerBI.  I have taken the time to address everything (except separating the Fact and Dimension tables, which I plan to do with time)

If you are able to help me see why the measure is not achieving the logic I'm aiming for above, I would be grateful for any pointers.  I have a feeling I am missing something in my logic to achieve this.

2. I don't see the model, so I can't say anything. For instance, you don't say what's Started and why you are calculating a RT using a column in a fact table instead of a Calendar.

"Started" is the date of this issue submission - I now have created the Calendar table and created a relationship between Table.started and Calendar.date.  

I also realise now that I don't seem to need the runnit total measure ontop of this measure you are helping me with since it seems to be giving me a total.


3. Time-intel functions work properly only on proper Calendars.

4. When a measure depends on Calendar, you have to use the Calendar when showing values and calculating time-dependent values. You either cannot use any other columns or you have to know what and why you are doing what you're doing.

5. No columns from a fact table can be exposed. They must be hidden. Slicing can only be done via dimensions. If you don't do this, sooner or later you'll create a monster, hard to understand and hard to code against.

6. Your running total (from the beginning of time) is not what it should be and it'll probably never be because the measure [Open Issues] is dependent on DATESYTD function, so it'll always return data for the latest currently visible year.

7. Which field is the Calendar joining to the fact table on?

In a word, I think (from what you've shown) many things are incorrect here.

Best
D

---

[Surfacescan]

I know this is a bit of a wild example, however from the table below - I would expect the daily count of open issues to never exceed 3 because there are only 3 uers (key = phone number) who are able to open a ticket.  And one user can only have one ticket open at any given time.  So if they appear to be opening more tickets - the ceiling should be one.

And when one  continually closes his ticket (false or blank) it should only decrement for that user. 

so on May 7th and May 8th below, the daily running total should remain at 2 because users 111 and 222 still have open tickets, even though 333 had closed his twice on two days.

[Surfacescan]

Hi @Anonymous - thank you for all the help you've given.  If you get a chance to review the updated info I've provided about the functionality I want to achieve with this measure, I would appreciate it.

Thanks - I would love to resolve this, with a bit more help from you on the measure.

[Anonymous]

I'll have a look at this later today. I'm right now in the process of changing jobs and this is why I'm a bit short of time... Sorry.

Best
D

[Surfacescan]

Wow - all the best in your transition! 

[Anonymous]

Hi there.

Thanks for your wishes 🙂 I've already made the switch.

I've looked at your posts... I think for the open issues you need a different measure. Such a measure should not depend on DATESYTD. But I can't really help because I can't see the whole model. I think your Calendar should be joined to the Started field in the fact table and this field therefore should never be used to slice by. No field from a fact table should ever be used to slice by it.

// Open issues tries to calculate open issues
// in the current period of time. It might or might
// not work correctly depending on your definitions
// of what an open issue is and how to calculate it.
// But I don't really know the full problem so I can
// only give you some guidance.
[Open Issues] =
	VAR __allIssues =
	    calculate(
			COUNT ( 'FactTable'[Phone] ),
			// Again, you should have [Open Tent Issues]
			// either in a dimension and then it would
			// be exposed and you'd need to use KEEPFILTERS
			// or it should be hidden and part of the FactTable
			// in which case you should not expose it and
			// remove KEEPFILTERS. Currently, you have a mix
			// that is the worst thing you can do, even though
			// it'll work correctly from the functional point
			// of view.
			KEEPFILTERS( 'FactTable'[Open Tent Issues] )
	)
	VAR __resolvedIssues =
	     calculate(
	         COUNT ( 'FactTable'[Phone] ),
	         // Same remark as above applies.
	         KEEPFILTERS( NOT( 'FactTable'[Open Tent Issues] ) )
	     )
	
	VAR __openIssues = __allIssues - __resolvedIssues
	VAR __openIssuesExist = 
		divide( __openIssues > 0, __openIssues > 0 )
	RETURN
		__openIssues * __openIssuesExist
		
		
// This is like a RT on the Calendar
// dimension but only throughout the year.
// It's just a Year-To-Date calculation.
[Open Issues YTD] =
	CALCULATE(
		[Open Issues],
		DATESYTD( Calendar[Date] )
	)
	
// This is a RT from beginning of time.
[Open Issues RT (absolute)] =
	CALCULATE(
		[Open Issues],
		Calendar[Date] <= MAX( Calendar[Date] )
	)
	
// This is a running total throughout selected
// periods of time from Calendar and please remember
// that a measure that uses ALLSELECTED must never
// ever be used in a different measure that does
// iteration. NEVER.
[Open Issues RT (relative)] =
	CALCULATE(
		[Open Issues],
		KEEPFILTERS( Calendar[Date] <= MAX( Calendar[Date] ) ),
		ALLSELECTED( 'Calendar' )
	)
	
// Please note that you should not use columns
// from your fact table to slice and dice. If you
// do, you'll be shooting yourself in the foot.

Best
D

[Surfacescan]

On further reflection, I wonder if the count of open/resolved issues is not being applied just to one user/key and instead is a running total across all open/resolved tickets.  Perhaps the running total measure is confounding that...