Forum Discussion
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
22 Replies
Data model joins are generally behaving like left outer joins, and that is perfectly fine for the vast majority of situations.
see attached
those are slightly different concepts. Read about AutoExists.
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
hi,
try this measure,
dynamic filter =
calculate(
countrows( transport[period_key])
)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
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 !!!
LinkedInHello 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 :(.
And what makes you choose a bidirectional join?
