Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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 🙂
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
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 🙂
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
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
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!
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:
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.
@@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.
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
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.
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.
Wow - all the best in your transition!
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
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...
User | Count |
---|---|
47 | |
44 | |
19 | |
14 | |
14 |
User | Count |
---|---|
107 | |
56 | |
28 | |
20 | |
16 |