CODEWITHSUNDEEP
excelsql-server-2012ssaspowerpivotpowerview
K4timini
K4timini

Reputation: 741

PowerPivot - How to create a INNER JOIN between multiple sources

I'm working on a PowerPivot project and I need to create a join between a SQL Server query retrieving a list of products (including prices) and an Excel data sheet containing products and other prices. The objective is to compare the price of my fake store to a fake concurrent store.

My SQL Query return all the products of my store :

P1 $10
P2 $20
P3 $33
P4 $44

etc...

The Excel data sheet contains only some products with different prices :

P1 $11
P2 $15

Then, in my PowerView data sheet I can compare prices including a KPI to easily see if my fake price is less expensive than the other store :

Product | Our price | Their Price | KPI status
P1      | $10       | $11         | Yes !
P2      | $20       | $15         | No !

It works well, however it displays all products of my store even if there is no row in the data sheet. In fact, I would like to display only rows matching with the Excel data sheet.

So, how to create an "inner join" insteed of a "left join" ?

Upvotes: 0

Views: 6176

Answers (1)

Vipin Bihari
Vipin Bihari

Reputation: 41

Use DAX expression to join tables . this will resolve join issues. syntax for inner join (here a and b are two tables )

EVALUATE
FILTER (
    CROSSJOIN ( a, b ),
    a[key] = b[key]
)

Upvotes: 0

Related Questions