0

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" ?

K4timini
  • 711
  • 2
  • 14
  • 34
  • Can you not just filter your table or the entire view on Their Price is not blank? – mmarie May 20 '14 at 03:23
  • How to filter data after a join ? In fact, there are two tabs on PowerPivot, one containing the full list of products and associated prices and the other one only some products associated to other prices. Blank values appears after the join (or left join) because some products of the first tab as not referenced in the second one. – K4timini May 20 '14 at 07:14
  • Sorry, filter it in Power View, not in the model. If you really want to filter it in the model, create a calculated column in your products table =related('Other Store'[Product]). Then filter that column so it is not blank. – mmarie May 20 '14 at 15:40
  • which field are you using on rows? if you use the one that you have stored in Excel and provided the relationship is setup correctly, it should display only those that are mentioned in the given table (= it actually does inner join for you). – Petr Havlik May 20 '14 at 20:58
  • In PowerPivot, I used the product's ID to describe the relationship between these two data sources. => TABLE1 : SQL Query (ID, Product name, price) (~25000 rows) => TABLE2 : Excel (ID, Product name, price) (~1000 rows) In PowerView, I added to a table the following fields : (TABLE1.ProductName, TABLE1.price, TABLE2.price) It displays me all products even if TABLE2.price is empty (because it doesn't appear in the TABLE2). And because it do that, my KPI based on these two prices is wrong because TABLE2.price is null. – K4timini May 21 '14 at 07:45

1 Answers1

0

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]
)
Vipin Bihari
  • 41
  • 4
  • 1
    Newbie here... where do I add this expression to implement the join? – Kevin Buchan Oct 03 '17 at 19:41
  • I didn't find any direct way. So, I duplicate a sheet where a table is directly linked to the model. Then I go to the new tab, right click, table, edit DAX... Then you have a small, ugly text box where you can input your DAX expression. – Robert Jun 29 '21 at 06:28