PBI perform inner or outer join

[ondrej-cdc]

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

[Anand24]

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.

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

[ondrej-cdc]

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 :(.

[lbendlin]

And what makes you choose a bidirectional join?

[ondrej-cdc]

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

[lbendlin]

No, he suggested unidirectional.

[ondrej-cdc]

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?

[Anand24]

@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.

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

[ondrej-cdc]

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.

[Anand24]

@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?

[ondrej-cdc]

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.

[Anand24]

@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

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

[ondrej-cdc]

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

[lbendlin]

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

[santiagomur]

hi, 

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

[ondrej-cdc]

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.

Thank you in advance,

Ondrej

[ondrej-cdc]

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

[lbendlin]

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.

[ondrej-cdc]

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]

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

see attached