Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ondrej-cdc
Frequent Visitor

PBI perform inner or outer join

Hello everyone,
I would like to aks you about data modeling in Power BI. If I choose my tables set like star schema at model I dont understand how to set inner join or outer join. If I will be specific there is my fact table and dimension table with dates.

 

Fact table named "transport"
"id";"period_key";"vehicle_key";"kg"
"1";"20230801";"12";"22,0"
"2";"20230801";"14";"24,0"
"3";"20230901";"12";"21,0"
"4";"20230901";"14";"27,0"
"5";"20230801";"14";"4,0"
"6";"20231001";"5";"6,0"
"7";"20231001";"10";"2,0"

 

Dimension table with dates named "dates"
"period_key";"quarter","name"
"20230801";"Q3";"August"
"20230901";"Q4";"September"
"20231001";"Q4";"October"
"20231101";"Q4";"November"

 

Then a set up join with "period_key" and I would like to filter in report with dates.name, I want to have to choose only with matching values. So at the filter there have to be values to choose August, September, October. How this could be solved? And if I have a much more dimension tables?


Thank you in advance,
Best regards,
Ondrej

19 REPLIES 19
Anand24
Super User
Super User

Hi @ondrej-cdc ,
I feel you just need to join Dim table to Fact table on period_key with uni-directional filtering.

Now you just need to take the field from Dim table into slicer/filter. Non-matching values from Fact table would be automatically neglected in the slicer.

PBI_SuperUser_Rank@1x.png  

Give a Thumbs Up if this post helped you in any way and Mark This Post as Solution if it solved your query !!!

Proud To Be a Super User !!!
LinkedIn

Hello Anand24, I am sorry, but I think the issue was solved, but a had applied an filter to distinctcount 😞 If I have set both-directional join, the non matching values are still here :(.

 

{B27AC675-20F6-4BA9-A0E6-A31B53BB801D}.png{66317E6F-6209-40C1-8223-AAD2F3049CF7}.png

And what makes you choose a bidirectional join?

It is from Anand post. Is It not right?

I feel you just need to join Dim table to Fact table on period_key with uni-directional filtering.

Ondrej

No, he suggested unidirectional.

Ok, but I am a little bit lost.. so I set up the join back to one direction? But I can not change the direction of filter, it is always from dates dimension to transport fact table (1:Many). Could you please share some screenshots?

@ondrej-cdc ,
It rightly should be from Date Dim Table to Transport Fact table. Which means all values from Date dim table are considered while only matching values from Fact table will appear. Hence, you can take Month from Date Dim Table into slicer which will mean only matching months from Transport Fact would be driven/filtered.

Ideally, your dim table should filter out your fact table. In SQL terms, it is simply Date Dim Table LEFT JOIN Transport Fact Table on Period_Key = Period_Key.

PBI_SuperUser_Rank@1x.png  

Give a Thumbs Up if this post helped you in any way and Mark This Post as Solution if it solved your query !!!

Proud To Be a Super User !!!
LinkedIn

I think I have my model set up correctly as you write.. but still havent correct results. And it needs to be inner join, I want to have in filter non matching values with fact table.

 

{64258218-CF43-46B0-A75B-88279AAD871B}.png{E626C6C7-CF94-484F-89BB-89C2C62CBBDB}.png

@ondrej-cdc ,
You want to see the non-matching values from fact table on your visualization or you want to completely remove/filter out non-matching values from fact table in your data analysis?

I want to remove the member November from filter (vere possible kind), because it has not a fact data at transport table.

 

"id";"period_key";"vehicle_key";"kg"
"1";"20230801";"12";"22,0"
"2";"20230801";"14";"24,0"
"3";"20230901";"12";"21,0"
"4";"20230901";"14";"27,0"
"5";"20230801";"14";"4,0"
"6";"20231001";"5";"6,0"
"7";"20231001";"10";"2,0"

 

So there is no measure values of period_key=20231101, so I dont want to show this value to user.

@ondrej-cdc ,
Try below:
1. Simply take date/period_key from fact table into slicer. Make the relationship bi-directional(Not Recommended)
2. In Power Query Editor, Perform Fact Table Left Join Dim Table, remove Dim Table's Date/period_key and keep only Fact Table's Date/Period_Key

PBI_SuperUser_Rank@1x.png  

Give a Thumbs Up if this post helped you in any way and Mark This Post as Solution if it solved your query !!!

Proud To Be a Super User !!!
LinkedIn

Ok, thank you very much. I just thought, that there will be some approach above star schema, not to create join at Power Query level and send to report flat table. I thought this is a basic thing, perform inner or outer join.. Guess there will be some reasons, like Ibendlin wrote that it is usual practise show innactive members and so.

If anyone find another approaches I will be thankful.

Thank you very much all of you,

Best regards Ondrej

Your data model needs to follow your business question.  If your business question changes then adjust your model accordingly.

santiagomur
Resolver II
Resolver II

hi, 

try this measure,
dynamic filter = 
calculate(
         countrows( transport[period_key])
)

santiagomur_0-1697442915072.png

 

 

Hello, thank you for your advice, but I have the following error and I couldnt find correction. See a screenshot attached. Error says: A single value for column 'period_key' in table 'transport' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

 

{3AF3BD97-A79D-415E-9495-6D7B2CCD2854}.png

Thank you in advance,

Ondrej

ondrej-cdc
Frequent Visitor

Hello, thank you very much, so a filter of do not show members without measure values is applied automatically, I got it, but is some way to show the member values of dimension dates.name in the filter similar way? If there will be much more members, it could be confusing if there are members without matching fact data, or I am not right and it is usual practise (user can see the members with measure values in the table or chart)? 

 

Thank you,

Best regards Ondrej

yes, pretty much usual practice.  If you have a particular issue please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

Ok, thank you. The question was meant in general. But it could be relate to sample data, then the issue would say, dont have a not matching members at filter. Hope someone maybe would know some approach

lbendlin
Super User
Super User

Data model joins are generally behaving like left outer joins, and that is perfectly fine for the vast majority of situations.

 

lbendlin_0-1697161166064.png

 

 

lbendlin_1-1697161257099.png

 

see attached

 

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors