why Power bi does left join implicit in the table visual?

[Anonymous]

I have a simple table in PowerBI, where I combine a FactTable with hours and the Employee dimension.

My problem now is that, I created a calculated table with a subset of the employees:

=SELECTCOLUMNS(

    FILTER(

        'Employee,

        LOOKUPVALUE(

            ‘Mitup'[Employee Number]

            ,’Mitup'[Employee Number]

            ,'Employee’[Employee Number]

        )

    )

    ,"Name" ,'Employee'[Employee]

    ,"Employee Number" ,'Employee'[Employee Number]

)

(in the model I created a relationship between SubsetEmployee and FactTable)

And of course; the Power BI seems to automatically do a “left join”, and I wanted a ”inner join”.

That is, I want to get rid of that first row that displays blanks and a very high number of hours, I want to have an “inner join”, how can I achieve this?

(preferably without modifying the Hours and M Hours measures…)

[Anonymous · Accepted solution]

Hi @Anonymous 

Power BI will calcualte the result based on column, so you need to filter your your key column to make your result like inner join. If you don't filter your key column in your calculated table, you will get a result look like left join.

If you want to create a table by inner join, you can try summarize and in function to achieve your goal. 

Fact Table:

SubEmployee table:

Inner Join by Dax:

Table = 
SUMMARIZE (
    FILTER (
        SubsetEmployee,
        SubsetEmployee[Employee Number] IN VALUES ( 'Fact Table'[Employee Number] )
    ),
    SubsetEmployee[Employee Number],
    SubsetEmployee[Employee],
    "Hours", SUM ( 'Fact Table'[Hour] )
)

Result is as below.

Inner Join by Power Query:

For reference: Combine queries

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , It left or right on the fact side. If data is missing dimension it will be shown. Check why there is a blank first. Why dimension table is missing values.

You can add filter in measure or filter in filter pane to check for non blank value for the column in use.