how can i achieve this logic in power bi by using look up

[Anonymous]

Hi all

I have data like below table

sequence	ID	Field 1	Field 2	Field 3
1	A1			ZZZ
2	A1		YYY	ZZZ
3	A1	XXXX	YYY	KKK
1	A2
2	A2	XXXX		ZZZ
3	A2	MMM		ZZZ
4	A2	XXXX	YYY	ZZZ
1	A3	XXXX
2	A3	XXXX		ZZZ
1	A4	XXXX	YYY	ZZZ
1	A5

I want to create a new table based on below requirement

for ID = A1 

• "Field 1" was filled on the third sequence 
• "Field 2" was filled on the second sequence
• "Field 3" was filled on the first sequence but it's updated on the third sequence 

Expected Output:

ID	Field 1 sequence	Field 2 sequence	Field 3 sequence
A1	sequence 3	sequence 2	sequence 3
A2	sequence 4	sequence 4	sequence 2
A3	sequence 1	No Record	sequence 2
A4	sequence 1	sequence 1	sequence 1
A5	No Record	No Record	No Record

[Zubair_Muhammad]

@Anonymous 

See file attached as well with your sample data

[Anonymous]

Thanks Zubair_Muhammad

Is it possible to create expression in calculated dimension? because I want to show data in a chart like below image

[Zubair_Muhammad]

@Anonymous 

You mean calculated column ??

because we cannot show MEASURES in Axis.... right??

I think its possible.

I will check and get back to you after a while as I got to go out now.

[Zubair_Muhammad · Accepted solution]

@Anonymous 

Try this MEASURE

Field 1 Sequence =
VAR LastSeq =
    CALCULATE ( MAX ( Table1[sequence] ), Table1[Field 1] <> "" )
VAR ValueAtLastSeq =
    CALCULATE ( VALUES ( Table1[Field 1] ), Table1[sequence] = LastSeq )
VAR MinSeq =
    CALCULATE ( MIN ( Table1[sequence] ), Table1[Field 1] = ValueAtLastSeq )
VAR LastChange =
    TOPN (
        1,
        CALCULATETABLE (
            VALUES ( Table1[sequence] ),
            FILTER (
                Table1,
                [sequence] < LastSeq
                    && [Field 1] <> ValueAtLastSeq
                    && [Field 1] <> ""
            )
        ),
        CALCULATE ( MAX ( Table1[sequence] ) ), DESC
    )
VAR Result =
    SWITCH (
        TRUE (),
        NOT ( ISBLANK ( LastChange ) ), LastSeq,
        MinSeq < LastSeq, MinSeq,
        LastSeq
    )
RETURN
    IF ( ISBLANK ( Result ), "No Record", "Sequence " & Result )