Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi
I have a dataset that looks like follows:
Case ID ProcessDateTime Process2.0DateTime Error Codes Value
100 2/01/2023 1/01/2023 1.01 0
100 2/01/2023 1/01/2023 1.02 1
100 2/01/2023 4/03/2023 1.01 0
100 2/01/2023 4/03/2023 1.02 0
100 2/01/2023 3/03/2023 1.01 1
100 2/01/2023 3/03/2023 1.02 0
Note: There are duplicate Case IDs and there are more rows with different ProcessDateTimes.
I have made 2 calculated columns where
1. MinProcessDateTime is the EARLIEST Date recorded for a CaseID - in this case it is 2/01/2023, however this list is larger so there are more dates captured here.
2. The Proc2LastStage looks at the LATEST Date recorded for Process 2.0Date with the condition that the date is EARLIER than the MinProcessDateTime - meaning the result is 1/01/2023 as there are only two dates that are before 2/01/2023 (1/01/2023 and 12/12/2022).
Case ID ProcessDate Process2.0Date Error Codes Value MinProcessDateTime Proc2LastStage
100 2/01/2023 1/01/2023 1.01 0 2/01/2023 1/01/2023
100 2/01/2023 1/01/2023 1.02 1 2/01/2023 1/01/2023
100 2/01/2023 4/03/2023 1.01 0 2/01/2023 1/01/2023
100 2/01/2023 4/03/2023 1.02 0 2/01/2023 1/01/2023
100 2/01/2023 12/12/2022 1.01 1 2/01/2023 1/01/2023
100 2/01/2023 12/12/2022 1.02 0 2/01/2023 1/01/2023
What i need to solve for is the # Errors based on the two criteria above and for each unique case ID.
This means I need a table in the Visualisation/Report tab that can show me
Case ID #Errors
100 1
I was unsure what steps to take so I created a calculated table to show only Distinct Case IDs with their respective MinProcessDateTime and Proc2LastStage using the formula below
CalcTable=
SUMMARIZECOLUMNS(FactTable[caseid], "MinProcessDatetime", MIN(FactTable[MinProcessDateTime]), "Last Stage2", MAX(FactTable[Proc2LastStage]))
Then I tried to do a measure
# Errors M =
VAR caseidvar =
VALUES('CalcTable'[caseid])
VAR firstveroutcomedate =
VALUES('CalcTable'[MinProcessDateTime])
VAR laststagedate =
VALUES('CalcTable'[Proc2LastStage])
RETURN
CALCULATE(
SUM(FactTable[Value]),
'FactTable'[caseid] IN caseidvar
&& 'FactTable'[ProcessOutcomeDate] IN firstveroutcomedate
&& 'FactTable'[Proc2LastStage] IN laststagedate,
CROSSFILTER('CalcTable'[caseid], FactTable[caseid], Both)
)
But this didn't seem to give the right results, and when I put it on a vis, it doesn't seem to interact with any date filters, etc.
I don't know if it's necessary to duplicate MinProcessDateTime and Proc2LastStage in a separate table, I was just trying to create a column of distinct Case IDs.
I would really appreciate your help!
Maybe it's a matter of SUMX? But how do you SUMX on duplicate CaseIDs and where values in one Column should = Column2 (e.g. ProcessDateTime = MinProcessDateTime & Process2.0Date = Proc2LastStage)?
Thanks so much in advance
Try
# Errors =
CALCULATE (
SUM ( FactTable[Value] ),
FactTable[ProcessDateTime] = FactTable[MinProcessDateTime]
&& FactTable[Process2.0Date] = FactTable[Proc2LastStage]
)
Hi @johnt75 ,
I'm not sure if this is evaluated against each case ID.
There are duplicate Case IDs in the dataset
Thanks so much
It would be evaluated against each row which matches the criteria, i.e. ProcessDateTime = MinProcessDateTime and Process2.0Date = Proc2LastStage. In the sample data you provided it would match the first 2 rows only,giving a sum of 1.
Thank you @johnt75 however there may be instances where another case ID has the same process date time and proces2date time stamps.
Is there a way in which I can ensure I am looking at unique case IDs?
I am currently matching on another attribute - Person who worked on the case which is then matched to the segment/department they are in. The final visualisation will show something like the below -
Segment 1 31/03/22 30/04/22
Number of Errors 330 456
Segment 2
Number of Errros 345 843
Hence why I believe I will need to ensure Case IDs should be considered in the formula but I just don't know how
I think this should do it
# Errors =
SUMX (
VALUES ( FactTable[Case ID] ),
CALCULATE (
SUM ( FactTable[Value] ),
FactTable[ProcessDateTime] = FactTable[MinProcessDateTime]
&& FactTable[Process2.0Date] = FactTable[Proc2LastStage]
)
)
User | Count |
---|---|
77 | |
74 | |
63 | |
61 | |
45 |
User | Count |
---|---|
108 | |
102 | |
93 | |
83 | |
64 |