Can we have Outer join instead of Inner join in Table visualisation

[chayanupadhyay]

Hi All,

I have table visualisation which is having fields from 3 tables and showing data but it is creating inner join (as per my understanding table visualisation will do inner join between 3 tables) but i need to do outer join so whether the keys are matching in other table or not the left table data should display with other as null.

Is there any way to do the same?

I have trield "show items with no data" option but while i apply this on any of the column then data will not show irrespective of keys but the column where i applied "show itmes with no data" will not hold any data but it is having data( if i remove show items with no data then i am able to see the field data).

I am connected to Azure analysis service in Live connection mode.

Please let me know if you need more information.

Please let me know if someone has any idea how to achieve this.

Thank you,

Chayan

[v-shex-msft]

HI @chayanupadhyay,

>>Is there any way to do the same?

Actually, power bi doesn't any inner join between these tables, it will try analysis records from whole tables based on relationships.(similar as select * from a,b,c,d) 

In my opinion, you can try to write measure to display non blank records to instead drag original column fields to visuals.

Regards,

Xiaoxin Sheng

[chayanupadhyay]

Thank you @v-shex-msft for your reply.

The Scenario is, Fields coming in table visusals from customer, organization, contact and licnese dimensions..

I have one card visual where i am taking distinct count of Customer so basically that card is to verify that number on card should match with row count of table visual.

Now if i select anything in Slicer on report, my customer get filtered out on the slicer and i get unique count of customer for that particular slicer but my table visual doesn't give any data because may be the customer who got filtered out due to slicer their customer key is not matching in other dimension so now my validation doesn't work.

I want to see unique customers in table viusal irrespective of their keys matching or not means its sort of left outer join so that is why i was looking for something to achieve the same.

If the solution provided by you (create a measure to show non blank values) will work on above scenario then can you please elaborate more about it... what kind of DAX should i write to make it happen?

Thank you and apologies for this much larget content .. just wannt to be sure that i am able to explain the problem.

Regards,

Chayan

[v-shex-msft]

Hi @chayanupadhyay,

I think I have misunderstood your requirement.
According to your description, I think you can try to use below measure formula to get distinct customer count from out join table.

Customer count =
COUNTROWS (
    DISTINCT (
        SELECTCOLUMNS (
            NATURALLEFTOUTERJOIN ( tableA, TableB ),
            "Customer", [Cusomter]
        )
    )
)

NATURALLEFTOUTERJOIN Function (DAX)

Regards,

Xiaoxin Sheng