cancel
Showing results for
Did you mean:
Member

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

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
1 ACCEPTED SOLUTION

Accepted Solutions
Super User III

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

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
4 REPLIES 4
Super User III

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

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
Super User III

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

See file attached as well with your sample data

Try my new Power BI game Cross the River
Highlighted
Member

Thanks

Super User III

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

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

Announcements

#### 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!

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?

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

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

Top Solution Authors
Top Kudoed Authors