Forum Discussion
PBI perform inner or outer join
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 !!! |
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.
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
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 !!!
LinkedInOk, 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.
