Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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.
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 Make the key before you unpivot the table in Power Query e.g. Add Custom Column for key then Unpivot
@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,
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
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..
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
74 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |