Filtering based on from and to airport

[Anonymous]

Hi,

I have this table called fact Table, and also I have table called Airport.

In this fact table I have column named Airport From and Airport To. 

In the Dimension, I have the Airport code.

The purpose is, if I create filter with the Airport code from Dimension example CGK

it will show the data based like this

Airport From                                   Airport To

CGK                                                  SYD

CGK                                                  NRT

HND                                                 CGK

INC                                                   CGK

So it will show the From and To based on selection.

Help will be very appreciated.

Thanks,
Regards,

Connie

[Anonymous]

Create a key if you don't already have one. I would probably use a concatenation of Airport From & Airport To e.g. CGK-SYD

In Power Query unpivot Airport From and Airport To columns. You'll now have one column that has only Airport From or Airport To, and another column with the Airport Codes. Use the new column that has only Airport Codes as your filter/slicer.

To view in a table, use your key or another identifier to display to From and To correctly.

[Anonymous]

Hi @Anonymous,

Thank you for your answer,

Already try the unpivot it sounds great. But because I do unpivot it seems I can't decide what is the Key for Airport Dimension to make realtionship between Airport Dimension and the Fact Table. Can you help to suggest?

Thank you!

[Anonymous]

@Anonymous Make the key before you unpivot the table in Power Query e.g. Add Custom Column for key then Unpivot

[Anonymous]

@Anonymous Yes I create before unpivot it. I create the key Departure - Arrival eg CGK - SYD one and now it has more than 1.

example:

Airport ID	Value	Airport
CGK - SYD	Airport To	CGK
CGK - SYD	Airport From	SYD
CGK - NRT	Airport To	CGK
CGK - NRT	Airport From	NRT
NRT - CGK	Airport To	NRT
NRT - CGK	Airport From	CGK

Then when I do remove duplicate for Airport ID (Because key can't duplicate)

Airport ID	Value	Airport
CGK - SYD	Airport To	CGK
CGK - NRT	Airport To	CGK
NRT - CGK	Airport To	NRT

Will become like that, and when I try to filter CGK, it only show the data Airport To CGK only, Airport From CGK is not appear at all.

Thanks,

[v-jiascu-msft]

Hi @Anonymous,

I would suggest you delete the relationship between the fact table and the dimension table and create a measure like below. You can either add it in the visual or hide it in the Visual Level Filter. Please also refer to the demo in the attachment.

Measure =
VAR selected =
    SELECTEDVALUE ( 'Table'[airport] )
RETURN
    CALCULATE (
        IF (
            MIN ( 'Table1'[Airport From] ) = selected
                || MIN ( Table1[Airport To] ) = selected,
            1,
            BLANK ()
        )
    )

Best Regards,
Dale

[Anonymous]

Hi @v-jiascu-msft

Thank you for your help! It looks good, but when user not selecting any filter it will be blank. Is it any way to make it not blank?

For now I just duplicate the airport table so I have 2 airport table, but 1 table is unique, so I can create the relationship between 1st airport and fact. And it kinda works..

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

Try this one, please.

Measure =
VAR selected =
    VALUES ( 'Table'[airport] )
RETURN
    IF (
        MIN ( 'Table1'[Airport From] ) IN selected
            || MIN ( Table1[Airport To] ) IN selected,
        1,
        BLANK ()
    )

Best Regards,
Dale