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

Filtering based on from and to airport

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

1 ACCEPTED SOLUTION

Accepted Solutions
v-jiascu-msft Super Contributor
Super Contributor

Re: Filtering based on from and to airport

Hi @conniedevina,

 

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 REPLIES 7
jd009 Member
Member

Re: Filtering based on from and to airport

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.

Re: Filtering based on from and to airport

Hi @jd009,

 

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!

jd009 Member
Member

Re: Filtering based on from and to airport

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

Re: Filtering based on from and to airport

@jd009 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 IDValueAirport
CGK - SYDAirport ToCGK
CGK - SYDAirport FromSYD
CGK - NRTAirport ToCGK
CGK - NRTAirport FromNRT
NRT - CGKAirport ToNRT
NRT - CGKAirport FromCGK

 

 

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

Airport IDValueAirport
CGK - SYDAirport ToCGK
CGK - NRTAirport ToCGK
NRT - CGKAirport ToNRT

 

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 Super Contributor
Super Contributor

Re: Filtering based on from and to airport

Hi @conniedevina,

 

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 ()
        )
    )

Filtering-based-on-from-and-to-airport

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Filtering based on from and to airport

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 Super Contributor
Super Contributor

Re: Filtering based on from and to airport

Hi @conniedevina,

 

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 298 members 3,493 guests
Please welcome our newest community members: