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.
Hi
I need to obtain two calculate columns: 'Time2' and 'Cases'.
In Time2 column we have to calculate the sum of “Time” values, if the following conditions meet: (1) Company is “White” and (2) InitialGroup is diferent from FinalGroup. The sum should be conducted until the row in which the second condition meet. Otherwise, “KO” should appear.
Rows are grouped by ID.
An example:
ID | Company | InitialGroup | FinalGroup | Time | Time2 |
A3 | Black | G12 | G6 | 0,00 | KO |
A3 | White | G6 | G6 | 0,01 | KO |
A3 | White | G6 | G6 | 0,01 | KO |
A3 | White | G6 | G2 | 9,04 | 9,06 |
A3 | White | G2 | G2 | 206,07 | KO |
A3 | White | G2 | G2 | 9,15 | KO |
A5 | Black | G12 | G5 | 0,00 | KO |
A5 | White | G5 | G5 | 0,64 | KO |
A5 | White | G5 | G4 | 0,01 | 0,65 |
A5 | White | G4 | G4 | 0,03 | KO |
A5 | White | G4 | G3 | 0,10 | 0,13 |
A5 | White | G3 | G3 | 7,53 | KO |
I have tried using EARLIER functions, but I haven´t obtain good results.
Thanks,
Solved! Go to Solution.
Hi @Ley ,
Go to "edit queries">"Add column">"Index Column":
Then create a calculated column as below:
Times2 =
IF (
[Company] <> "White"
|| [InitialGroup] = [FinalGroup],
"KO",
VAR i = [Index]
VAR d = [ID]
VAR l =
CALCULATE (
MAX ( 'Table'[Index] ),
FILTER (
'Table',
'Table'[ID] = d
&& [Company] = "White"
&& [InitialGroup] <> [FinalGroup]
&& 'Table'[Index] < i
)
)
RETURN
CALCULATE (
SUM ( 'Table'[Time] ),
FILTER (
'Table',
'Table'[ID] = EARLIER ( 'Table'[ID] )
&& 'Table'[Index] <= EARLIER ( 'Table'[Index] )
&& 'Table'[Index]
> IF (
l = BLANK (),
CALCULATE ( MIN ( 'Table'[Index] ), FILTER ( 'Table', 'Table'[ID] = d )), l
)
)
) & ""
)
Finally you will see:
For the related .pbix file,pls click here.
Best Regards,
Kelly
Hi @Ley ,
Go to "edit queries">"Add column">"Index Column":
Then create a calculated column as below:
Times2 =
IF (
[Company] <> "White"
|| [InitialGroup] = [FinalGroup],
"KO",
VAR i = [Index]
VAR d = [ID]
VAR l =
CALCULATE (
MAX ( 'Table'[Index] ),
FILTER (
'Table',
'Table'[ID] = d
&& [Company] = "White"
&& [InitialGroup] <> [FinalGroup]
&& 'Table'[Index] < i
)
)
RETURN
CALCULATE (
SUM ( 'Table'[Time] ),
FILTER (
'Table',
'Table'[ID] = EARLIER ( 'Table'[ID] )
&& 'Table'[Index] <= EARLIER ( 'Table'[Index] )
&& 'Table'[Index]
> IF (
l = BLANK (),
CALCULATE ( MIN ( 'Table'[Index] ), FILTER ( 'Table', 'Table'[ID] = d )), l
)
)
) & ""
)
Finally you will see:
For the related .pbix file,pls click here.
Best Regards,
Kelly
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 |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |