Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Data: I have 1 table with item column which list records with errors, and another which has the violation date.
Error Log
Violation Id DimViolationDate
1 | 26/06/2018 | |
2 | 16/06/2018 | |
3 | 16/06/2018 | |
1 | 16/06/2018 |
|
Requirement: I want to find out number of errors which have been fixed since last time period(i.e from 16/06/2018 to 26/06/2018). In this case it would 2 as they show up in past period(16/06/2018) but not in current period(26/06/2018).
In the above case the result would be 2 because Violation IDs 2 & 3 show up in past period(16/06/2018) but not in current period(26/06/2018)
Steps I took:
1) Created a calculated table with data from current & last period
Current&PastItems =
VAR CurrentPeriod = CALCULATE(MAX(FactName[DimViolationDate]),FILTER(FactName,FactName[DimViolationDate]=MAX(FactName[DimViolationDate])))
VAR LastPeriod = CALCULATE(MAX(FactName[DimViolationDate]),FILTER(FactName,FactName[DimViolationDate]<>MAX(FactName[DimViolationDate])))
RETURN FILTER(ALL(FactName[Violation Id],FactName[DimViolationDate]),OR(FactName[DimViolationDate]=LastPeriod,FactName[DimViolationDate]=CurrentPeriod))
2) Calculated a field saying if it exists in last period & another if it exists in current period
Exists in Current Period = VAR CurrentPeriod = CALCULATE(MAX('Current&PastItems'[DimViolationDateKey]),FILTER('Current&PastItems','Current&PastItems'[DimViolationDateKey]=MAX('Current&PastItems'[DimViolationDateKey]))) VAR OccuranceInPeriod = CALCULATE(DISTINCTCOUNT('Current&PastItems'[Violation Id]),('Current&PastItems'[Violation Id]<>BLANK()), FILTER('Current&PastItems','Current&PastItems'[DimViolationDateKey] = CurrentPeriod))+0 RETURN IF(OccuranceInPeriod>=1,"Yes","No")
Exists in Last Period =
VAR LastPeriod = CALCULATE(MAX('Current&PastItems'[DimViolationDate]),FILTER('Current&PastItems','Current&PastItems'[DimViolationDate]<>MAX('Current&PastItems'[DimViolationDate])))
VAR OccuranceInPeriod = CALCULATE(DISTINCTCOUNT('Current&PastItems'[Violation Id]),('Current&PastItems'[Violation Id]<>BLANK()),ALLEXCEPT('Current&PastItems','Current&PastItems'[Violation Id]),
FILTER('Current&PastItems','Current&PastItems'[DimViolationDate] = LastPeriod))+0
RETURN IF(OccuranceInPeriod>=1,"Yes","No")
3) Count items which have a yes for last period and no for current period & number of occurances = 1
Number of Occurances = CALCULATE(DISTINCTCOUNT('Current&PastItems'[DimViolationDateKey]),ALLEXCEPT('Current&PastItems','Current&PastItems'[Violation Id]))
Number of Fixed Violations = CALCULATE(DISTINCTCOUNT('Current&PastItems'[Violation Id]),FILTER('Current&PastItems','Current&PastItems'[Exists in Last Period]="Yes" && 'Current&PastItems'[Exists in Current Period]="No" && [Number of Occurances]=1) ) + 0
Solved! Go to Solution.
Hi @Anonymous
Create calculated columns
cuurrent = MAX([DimViolationDate]) maxdate of each id = CALCULATE(MAX([DimViolationDate]),ALLEXCEPT(FactName,FactName[Violation Id])) fixed untill current = IF([cuurrent]=[maxdate of each id],1,0) fixed previous = CALCULATE(DISTINCTCOUNT(FactName[Violation Id]),FILTER(ALL(FactName),[fixed untill current]=0))
Best Regards
Maggie
Hi @Anonymous
Create calculated columns
cuurrent = MAX([DimViolationDate]) maxdate of each id = CALCULATE(MAX([DimViolationDate]),ALLEXCEPT(FactName,FactName[Violation Id])) fixed untill current = IF([cuurrent]=[maxdate of each id],1,0) fixed previous = CALCULATE(DISTINCTCOUNT(FactName[Violation Id]),FILTER(ALL(FactName),[fixed untill current]=0))
Best Regards
Maggie
Hi @Anonymous
If current date is 2018/6/26, there are two errors were fixed before 2018/6/26, (2->2018/6/16, 3->2018/6/16)
the error 1 is fixed at 2018/1/16, then this error happened again and is finally fixed in 2018/1/26.
Thus, "fixed previous" means:
Count of Errors which don't exist in current period but existed in last period
Best Regards
Maggie
Hi @Anonymous
Thank you for such detailed steps you applied.
However, it is difficult for me to understand your dataset.
As your requirement said, from the "Error Log", I can't find anything about "ast time period","current period" and detemine which show up in past period but not in current period.
I would like to suggest you give an example of your tables(maybe not the real data, just can explain your scenario), relationships between these tables and expected result.
Best Regards
Maggie
Hi @Anonymous
Please look at the article about How-to-Get-Your-Question-Answered-Quickly.
Best Regards
Maggie
@v-juanli-msft I appreciate the link, but not sure why you are referring me to that link? Is there something I messed up while posting my query. I seem to have followed everything from that link.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |