Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I'm looking to visualize state of a certain items over time. Each item has its state logged into a sort of a changelog (example):
ItemId | Updated | Status |
Raven | 12.04.2021 | Open |
Raven | 18.04.2021 | Closed |
Boogie | 19.04.2021 | Resolved |
Raven | 20.04.2021 | Resolved |
Raven | 23.04.2021 | Closed |
The goal is to be able to produce a similar set of measure-based report over this sample data:
Date | Total count | Count of "Closed" | Count of "Resolved" |
18.04.2021 | 1 | 1 | 0 |
19.04.2021 | 2 | 1 | 1 |
20.04.2021 | 2 | 0 | 2 |
21.04.2021 | 2 | 0 | 2 |
22.04.2021 | 2 | 0 | 2 |
23.04.2021 | 2 | 1 | 1 |
24.04.2021 | 2 | 1 | 1 |
25.04.2021 | 2 | 1 | 1 |
Note that even if version history has no data for 21 and 22.04.2021, the resulting report contains corresponding rows with correct values. Measure must produce result per any given point of granularity (be it day or hour or anything in general); this means for a table-like report with daily granularity used in the example, there must be rows for each day.
In general, I want to pick a point of time and calculate required aggregates (count() by status in this example) over data as it existed at this point of time. This seems to be quite common task but unfortunately, I wasn't able to find any examples.
EDIT: Sample data modified to include scenario when item changes it's state back and forth.
EDIT2: Stressed out that there must be no gaps in the report (measure must be calculated for each consecutive day if we use daily granularity, for each hour if we use hourly and so on)
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi @Anonymous ,
Here are the steps you can follow:
1. Create calculated table.
Date = SELECTCOLUMNS('Table',"Date",'Table'[Updated])
2. The relationship between connecting two tables
3. Create calculated column.
Count of "Closed" =
var _1=CALCULATE(COUNT('Table'[Status]),FILTER(ALL('Table'),'Table'[Status]="Closed"&&'Table'[Updated]=MAX('Date'[Date])))
return
IF(_1=BLANK(),0,_1)
Count of "Resolved" =
var _1=CALCULATE(COUNT('Table'[Status]),FILTER(ALL('Table'),'Table'[Status]="Resolved"&&'Table'[Updated]=MAX('Date'[Date])))
return
IF(_1=BLANK(),0,_1)
Total count = 'Table'[Count of "Closed"]+'Table'[Count of "Resolved"]
Result:
If the data is grouped by hours, I changed the data presentation without changing the Dax function:
Result:
The data is grouped by hour Count
Does this result meet your expectations.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello Liu!..
I do not see how calculated table of this kind changes anything - it's basically the same as version history table. In your updated example result still contains gaps:
The goal is to get count of items that had a specific status at specific point of time with given granularity - based on a "version history" log which does NOT have any specific granularity.
P.S. For some reason I am unable to post complete example here; it raises an error about message containing invalid HTML characters - which is infuriating as the message was totally composed by built-in editor...
Hi @Anonymous ,
According to your description, I create some data:
Here are the steps you can follow:
1. Create measure.
Count of "Closed" =
var _1=CALCULATE(COUNT('Table'[Status]),FILTER(ALL('Table'),'Table'[Status]="Closed"&&'Table'[Updated]=MAX('Table'[Updated])))
return
IF(_1=BLANK(),0,_1)
Count of "Resolve" =
var _1=CALCULATE(COUNT('Table'[Status]),FILTER('Table','Table'[Status]="Resolved"&&'Table'[Updated]=MAX('Table'[Updated])))
return
IF(_1=BLANK(),0,_1)
Total count =
'Table'[Count of "Closed"]+'Table'[Count of "Resolve"]
2. Result.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello.
Unfortunately, this will not work with a version table. A version table is a table where only changes to item state are logged: if item has no state changes over a certain period of time - there are no corresponding rows. And without them, we have 0s (incorrect values) for count of whatever status for dates where there are no rows.
In your example source data contains redundant rows: there is a row for every item and every day even if the state didn't change. For example, 2nd row here is redundant and wouldn't be present in a version table:
This could be a solution if Power BI allowed merging a CALENDARAUTO() table with a version table - but it doesn't, date table doesn't even appear in the table list for merge operation.
Then again, what if we need pre-hour granularity instead of per-day? Per-minute?
Any ideas here?
Hello Ashish. Thank you for the example file. I tried to understand the logic mehind the measures, however it seems there are several issues with the proposed solution:
1. Original data might span several years to DATESYTD does not apply here. I tried changing it to DATESBETWEEN and result was immediately incorrect.
2. The item might change its status back and forth. It could be in the same state more than once in a given period. If you modify the source data to include this case, the results are again incorrect (for example, negative values)
Hi,
You may download my PBI file from here.
Hope this helps.
Hello Ashish.
This would only work if the version history table contains exactly one row per date period you need in the report. In your example, there is exactly one row per day per item.
This is not the case for real datasets where item status change is not logged daily (hourly etc), but instead only logged when item status changes.
Hi,
That is exactly what my solution does. Please study the transformation steps in the Query Editor very carefully. I have transformed your data to make it appear as one row per date.
Well I marked this as solution, as it seemed to be the one, but I was wrong. This solution only applies when certain assumption about changelog data are valid. For example, you would get a type conversion error if any of the dates in changelog is different from 0:00:00 of a given date (e.g. contains time part):
In addition, the result would be incorrect if changelog contains more than one record for a given day period - because of the way the transformation parses the data.
This is very important that no assumptions regarding source data (changelog) can be made. It contains version history in format of timestamp-item-new status, but that's it. It's not guaranteed to have only one record per day, or per hour, or per minute.
Hi,
You may download my PBI file from here.
Hope this helps.
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |