Trying to substract the values based on one column to another column

[Anonymous]

Hey,

I am trying to substract the values based on one column to another column.

In the below table ID is having groups of Data,if the ID and the Cycle No is 1,and the faults are different the output should be 1.

If the Same ID and Cycle No are different faults are same then the output suould be substract with the same Cycle No (For Example ID:K09031 Cycle No:2 Fault: No then next Cycle No:6 same Fault: No then output should be 6-2=4 & ID:K09031 Cycle No:2 Fault: No then next Cycle No:8 same Fault: No then output should be 8-2=6)

If the Same ID and Cycle No are different faults are same then the output suould be substract with the same Cycle No (For Example ID:K11121 Cycle No:1 Fault: Damage then next Cycle No:3 same Fault: Damage then output should be 3-1=2 , ID:K11121 Cycle No:1 Fault: Damage then next Cycle No:5 same Fault: Damage then output should be 5-1=4, ID:K11121 Cycle No:1 Fault: Damage then next Cycle No:6 same Fault: Damage then output should be 6-1=5, ID:K11121 Cycle No:3 Fault: No then next Cycle No:6 same Fault: No then output should be 6-3=3 & ID:K11121 Cycle No:1 Fault: Edge then next Cycle No:6 same Fault: Edge then output should be 6-1=5)

Note: Here Id has indusival groups with cycle no's and different faults, that should substract with the same fault occurs in the same group with earlier cycle no has same fault occur.

An Example if my dataset is this with required Solution Column:

Id	Cycle No	Fault	Solution Column
KL0002	1	Edge	1
KL0002	1	Damage	1
K09031	2	Edge	2
K09031	2	No	2
K09031	6	No	4
K09031	8	With	8
K09031	8	No	6
K11121	1	Damage	1
K11121	1	Edge	1
K11121	3	No	3
K11121	3	Damage	2
K11121	5	Damage	4
K11121	6	With	6
K11121	6	Damage	5
K11121	6	No	3
K11121	6	Edge	5
KG1366	1	No	1
KG5560	1	With	1
H15238	5	Edge	5
H15678	1	No	1
HG1234	1	No	1
HG1234	1	Edge	1
H08542	2	With	2

Anyone with some ideas on how to fix this? Manually is not an option as there are thousands of datapoints.

Thanks & Regards,

Dharani

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

Solution column CC =
VAR mincyclenumber =
    MINX (
        FILTER (
            Data,
            Data[Id] = EARLIER ( Data[Id] )
                && Data[Fault] = EARLIER ( Data[Fault] )
        ),
        Data[Cycle No]
    )
RETURN
    SWITCH (
        TRUE (),
        Data[Cycle No] = mincyclenumber, mincyclenumber,
        Data[Cycle No] - mincyclenumber
    )

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

Solution column CC =
VAR mincyclenumber =
    MINX (
        FILTER (
            Data,
            Data[Id] = EARLIER ( Data[Id] )
                && Data[Fault] = EARLIER ( Data[Fault] )
        ),
        Data[Cycle No]
    )
RETURN
    SWITCH (
        TRUE (),
        Data[Cycle No] = mincyclenumber, mincyclenumber,
        Data[Cycle No] - mincyclenumber
    )

[Anonymous]

Thanks Kim Great Work.

Need one more help,

Id	Cycle No	Fault	Solution Column
KL0002	1	Edge	1
KL0002	1	Damage	1
K09031	2	Edge	2
K09031	2	No	2
K09031	6	No	4
K09031	8	With	8
K09031	8	No	2
K11121	1	Damage	1
K11121	1	Edge	1
K11121	3	No	3
K11121	3	Damage	2
K11121	5	Damage	2
K11121	6	With	6
K11121	6	Damage	1
K11121	6	No	3
K11121	6	Edge	5
KG1366	1	No	1
KG5560	1	With	1
H15238	5	Edge	5
H15678	1	No	1
HG1234	1	No	1
HG1234	1	Edge	1
H08542	2	With	2

If the Same ID and Cycle No are different faults are same then the output suould be substract with the same Cycle No (For Example ID:K09031 Cycle No:2 Fault: No then next Cycle No:6 same Fault: No then output should be 6-2=4 & ID:K09031 Cycle No:6 Fault: No then next Cycle No:8 same Fault: No then output should be 8-6=2)

If the Same ID and Cycle No are different faults are same then the output suould be substract with the same Cycle No (For Example ID:K11121 Cycle No:1 Fault: Damage then next Cycle No:3 same Fault: Damage then output should be 3-1=2 , ID:K11121 Cycle No:3 Fault: Damage then next Cycle No:5 same Fault: Damage then output should be 5-3=2, ID:K11121 Cycle No:5 Fault: Damage then next Cycle No:6 same Fault: Damage then output should be 6-5=1, ID:K11121 Cycle No:3 Fault: No then next Cycle No:6 same Fault: No then output should be 6-3=3 & ID:K11121 Cycle No:1 Fault: Edge then next Cycle No:6 same Fault: Edge then output should be 6-1=5)

I mean that in the earlier fault place need recent fault occurence in the recent Cycle Count

Thanks,

Dharani

[Jihwan_Kim · Accepted solution]

Hi,

Thank you for your message, and please check the attached file.

Solution column V2 CC =
VAR currentcyclenumber = Data[Cycle No]
VAR mincyclenumber =
    MINX (
        FILTER (
            Data,
            Data[Id] = EARLIER ( Data[Id] )
                && Data[Fault] = EARLIER ( Data[Fault] )
        ),
        Data[Cycle No]
    )
VAR maxcyclenumber_beforecurrent =
    MAXX (
        FILTER (
            Data,
            Data[Id] = EARLIER ( Data[Id] )
                && Data[Fault] = EARLIER ( Data[Fault] )
                && Data[Cycle No] < currentcyclenumber
        ),
        Data[Cycle No]
    )
RETURN
    SWITCH (
        TRUE (),
        Data[Cycle No] = mincyclenumber, mincyclenumber,
        Data[Cycle No] - maxcyclenumber_beforecurrent
    )

[Anonymous]

Hi Kim,

Need your help, if possible please help me.

Need help here to get solution of "Rank based on Serial No, Cycle Count & Fault Type" & "Rank based on serial No, Log Date and Fault Type". With calculated column(DAX) or Power Query is fine.

Rank based on Serial No, Cycle Count & Fault Type = Count of distinct Cycles when similar fault Type Occur

Rank based on serial No, Log Date and Fault Type = Count of distinct last different log Date when similar fault type logged

Fault Type	Serial No	Cycle Count	Log Date	Rank based on Serial No, Cycle Count & Fault Type	Rank based on Serial No, Log Date & Fault Type
Bent	S1256	2	10-Aug-21	1	1
Ink Failure	S1256	4	10-Sep-21	1	1
Self Stripping	S1256	8	31-Dec-21	1	1
Bent	S1256	8	31-Dec-21	2	2
Ink Failure	S1256	10	23-Feb-22	2	2
Bent	S1256	10	23-Feb-22	3	3
Broken Weld	S1256	10	23-Feb-22	1	1
Self Stripping	S1256	10	23-Feb-22	2	2
Broken Weld	S1256	10	26-Feb-22	1	2
Ink Failure	S1256	10	26-Feb-22	2	3
Self Stripping	S1256	10	26-Feb-22	2	3
Bent	S1256	10	26-Feb-22	3	4
Not Painted	S1256	10	26-Feb-22	1	1
Ink Failure	S1256	10	26-Feb-22	2	3
Bent	S1256	13	20-Apr-22	4	5
Require Stripping	S1256	13	25-Apr-22	1	1
Not Painted	S1256	13	25-Apr-22	2	2
Ink Failure	M083	1	17-Jan-22	1	1
Ink Failure	M083	1	17-Jan-22	1	1
Bent	M083	1	17-Jan-22	1	1
Bent	M083	1	17-Jan-22	1	1
Not Painted	M083	1	17-Jan-22	1	1
Not Painted	M083	1	17-Jan-22	1	1
Ink Failure	M083	3	3-Mar-22	2	2
Ink Failure	A863	1	11-Oct-21	1	1
Broken Weld	A863	1	11-Oct-21	1	1
Self Stripping	A863	1	11-Oct-21	1	1
Ink Failure	A863	1	14-Oct-21	1	2
Bent	A863	1	14-Oct-21	1	1
Ink Failure	A863	1	14-Oct-21	1	2

If you don't understand my communication regards this solution appoligies for that and please raise a question for clarification.

In Advance Thanks.

Thanks,

Dharani

[Anonymous]

Thanks a Ton Kim

This helps me alot

Thanks,

Dharani