cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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

Hi all

 

I have data like below table

sequenceIDField 1Field 2Field 3
1A1  ZZZ
2A1 YYYZZZ
3A1XXXXYYYKKK
1A2   
2A2XXXX ZZZ
3A2MMM ZZZ
4A2XXXXYYYZZZ
1A3XXXX  
2A3XXXX ZZZ
1A4XXXXYYYZZZ
1A5   

 

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:

 

IDField 1 sequenceField 2 sequenceField 3 sequence
A1sequence 3sequence 2sequence 3
A2sequence 4sequence 4sequence 2
A3sequence 1No Recordsequence 2
A4sequence 1sequence 1sequence 1
A5No RecordNo RecordNo Record
1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

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

@vengadeshpalani 

 

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 )
Try my new Power BI game Cross the River

View solution in original post

4 REPLIES 4
Super User III
Super User III

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

@vengadeshpalani 

 

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 )
Try my new Power BI game Cross the River

View solution in original post

Super User III
Super User III

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

@vengadeshpalani 

 

See file attached as well with your sample data

 

Try my new Power BI game Cross the River
Highlighted

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

Thanks 

 

 

Super User III
Super User III

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

@vengadeshpalani 

 

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.

Try my new Power BI game Cross the River

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors