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

No, he suggested unidirectional.

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

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

PBI perform inner or outer join | Microsoft Fabric Community

22 Replies

lbendlin
Super User
2 years ago
Data model joins are generally behaving like left outer joins, and that is perfectly fine for the vast majority of situations.

see attached

data model.pbix23 KB
- Anonymous
  Not applicable
  1 year ago
  If I give relationship between fact and dim as many to 1 in modelling, then what kind of join it is. Is it left join or inner join?
  - lbendlin
    Super User
    1 year ago
    those are slightly different concepts. Read about AutoExists.
ondrej-cdc
Frequent Visitor
2 years ago
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
  Super User
  2 years ago
  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
    Frequent Visitor
    2 years ago
    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
santiagomur
Resolver II
2 years ago
hi,

try this measure,
dynamic filter =
calculate(
countrows( transport[period_key])
)
- ondrej-cdc
  Frequent Visitor
  2 years ago
  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
Anand24
Super User
2 years ago
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
  Frequent Visitor
  2 years ago
  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
    Super User
    2 years ago
    And what makes you choose a bidirectional join?