skip next row duplicate value in Power BI desktop

[Anonymous]

Hi,

I'm using SSAS tabular as source for the PBI report above. 

Sorted data on Measure value (CapacityWeightTonnes) descending, to get top N Route Codes.

1st row 'JFK-LHR' is going route, and 2nd row 'LHR-JFK' is return route for the same Airline.

Refer few return Route Codes like LHR-JFK and LHR-DFW (highlighted red above).

Can you please help me remove/hide those return Route Codes in PBI report itself?

[Anonymous]

Hey @Anonymous

Try the following.

• Go to edit queries.
• Add a custom column
  

  Text.Start([RouteCode],3)

  
• Then remove duplicates based on that new column.

Good Luck! And let us know how it went.

Cheers!
A

[Anonymous]

Hi AClerk,

I'm using Live connection with SSAS Model, so won't able to add custom column as suggested in yoir post.

Can you please elaborate your approach in more detail, so I can see if it can be achieve slight different way

Thanks,

[Anonymous]

Hey,

As written above.

Not sure wats the logic behind your duplicates. But you might use the origin column?

Can you elaborate what is a duplicated record?
Cheers!
A

[Anonymous]

Hi,

Please look at 1st 2 rows in screen print (above original post), 

JFK-LHR

LHR-JFK 

If reposition Routes for 2nd row, it become equal to 1st row. And thats why wanted to skip the such occurances (like 2nd row)

[Anonymous]

Uh,

Got you now.

Forget about previous posts.

Sorry.

[Anonymous]

@Anonymous 

Please provide a sample that can be copied and pasted.

There should be a simple way to resolve this.

Thanks!
A

[Anonymous]

Hi,

Kindly find the sample data below,

RouteCode	CapacityWeightTonnes	originAirport	destinationAirport
JFK-LHR	11587.84	JFK	LHR
LHR-JFK	11581.14	LHR	JFK
DFW-LHR	11556.99	DFW	LHR
LHR-DFW	11528.65	LHR	DFW
ORD-DFW	9138.87	ORD	DFW
DFW-ORD	9129.79	DFW	ORD
ORD-LHR	8135.65	ORD	LHR
LHR-ORD	8100.96	LHR	ORD
LAX-LHR	6733.9	LAX	LHR
LHR-LAX	6733.9	LHR	LAX
EZE-MIA	6551.6	EZE	MIA
MIA-EZE	6544.9	MIA	EZE
MIA-DFW	6444.16	MIA	DFW
DFW-MIA	6406.29	DFW	MIA
GRU-MIA	6324.63	GRU	MIA
MIA-GRU	6310.46	MIA	GRU
NRT-DFW	5298.4	NRT	DFW
DFW-NRT	5249.46	DFW	NRT
PHL-LHR	5065.55	PHL	LHR
LHR-PHL	5037.49	LHR	PHL

Thanks,

[v-chuncz-msft · Accepted solution]

@Anonymous 

Drag measure below to Visual level filters and set Show items when the value is 1.

Measure =
VAR c =
    LOOKUPVALUE (
        Table1[CapacityWeightTonnes],
        Table1[originAirport], MAX ( Table1[destinationAirport] ),
        Table1[destinationAirport], MAX ( Table1[originAirport] )
    )
RETURN
    IF (
        SUM ( Table1[CapacityWeightTonnes] ) > c
            || (
                SUM ( Table1[CapacityWeightTonnes] ) = c
                    && MAX ( Table1[originAirport] ) < MAX ( Table1[destinationAirport] )
            ),
        1
    )

[Anonymous]

Thanks for the solution, but it works fine if don't have duplicate values either in Origin or Destination Airport.

It gives me error, while dealing with below sample data,

RouteCode	CapacityWeightTonnes	originAirport	destinationAirport
JFK-LHR	11587.84	JFK	LHR
LHR-JFK	11581.14	LHR	JFK
DFW-LHR	11556.99	DFW	LHR
LHR-DFW	11528.65	LHR	DFW
ORD-DFW	9138.87	ORD	DFW
DFW-ORD	9129.79	DFW	ORD
ORD-LHR	8135.65	ORD	LHR
LHR-ORD	8100.96	LHR	ORD
LAX-LHR	6733.9	LAX	LHR
LHR-LAX	6733.9	LHR	LAX
EZE-MIA	6551.6	EZE	MIA
MIA-EZE	6544.9	MIA	EZE
MIA-DFW	6444.16	MIA	DFW
DFW-MIA	6406.29	DFW	MIA
GRU-MIA	6324.63	GRU	MIA
MIA-GRU	6310.46	MIA	GRU
NRT-DFW	5298.4	NRT	DFW
DFW-NRT	5249.46	DFW	NRT
PHL-LHR	5065.55	PHL	LHR
LHR-PHL	5037.49	LHR	PHL
LHR-XXX	11500	LHR	XXX
JFK-LHR	11587.84	JFK	LHR
JFK-LHR	100.84	JFK	LHR

For example we have "JFK-LHR" duplicate values here.

How to handle it?

[Anonymous]

I see you also have origin airport.

Can use that as well.

But!
How do you know which is duplicated?
I think there is a flaw in your logic.

Thanks!
A