left outer join using dax, Multiple to Multiple

[apollo89]

Table 1:

Year  Month ID      PID

2017    02 90000 10000
2017    02 90000 10001
2017    02 90000 10002
2017    02 90000 10003
2017    02 90001 10004

Table 2:

Year Month ID Amt

2017    02 90000 10
2017    02 90001 20
2017    02 90002 30
2017    02 90003 40

Both these tables are pulled using power query and I am doing a left join between Table 1 (Left) and Table 2 (Right) within power query itself. I created a key in both the tables which concatenates Year, Month, and ID since in power query as multiple join statements are not allowed using power query GUI and I am joining on the same key.

As a result of this join, the values I am getting is:

Key                    ID    PID   Amt

2017029000    90000 10000 10
2017029000    90000 10001 10
2017029000    90000 10002 10
2017029000    90000 10003 10

And so on.

What I need to do is pivot this in Excel and just show '10' in Values field instead of (10+10+10+10) for the IDs and the PIDs.

Desired Output (In Pivot):

    ID        PID      Amt

90000     10000    10
               10001
               10002
               10003

I need a left join since there are many unmatched IDs in Table 1 which are required. Also, there are similar columns to PID in the table which are required attributes.

I had earlier tried to create a relationship between both these tables in pivot but since the Table 1 consists of duplicate IDs, it wouldn't let me do the same.

Can this be done in any way using DAX or in the pivot or even from the perspective of the join?

[v-xjiin-msft]

@apollo89 

To achieve your requirement, first we need to add a Index column for your table. Them we can create a calculated column to compare the current row with previous row with DAX expression. If they are same, then show blank.

Expression is like this:

New Amt =
IF (
    Table1[Table2.Amt]
        = LOOKUPVALUE ( Table1[Table2.Amt], Table1[Index], Table1[Index] - 1 ),
    BLANK (),
    Table1[Table2.Amt]
)

Same to ID column. 

Thanks,
Xi Jin.

[apollo89]

Hi Xi Jin,

Thank you for your reply. I have a slight change in my requirement. 

Since all the PID's (10000, 10001, 10002, 10003) are associated with just one ID (90000), they should show just one value i.e 10(which looks up from table 2). In the current way the Amt gets associated with the first PID and in pivotting this, if I were to filter on the other PID's I would lose the Amt. And the resultant format desired is of a pivot table.

I have been trying to create a measure which would show 10 in any filter context which has any of those numbers but to no avail.

Any ideas? 

Thanks Again!

[v-xjiin-msft · Accepted solution]

@apollo89 

First, you should know that Power BI is different than Excel. There's no such excel pivot table. If you want to display your data with the format like excel pivot table. I would suggest you to choose Matrix.

Then based on your description, if you don't filter on PID, you want to show only one value i.e 10. And when you filter on PID, you want to show 10 in any filter context. Right?

However this is a contradiction in Power BI. When we want to achieve the first condition, we have to make other 10s be blank. Then if we want to achieve your second condition, we need all the 10s are not blank. Thus, I'm afraid your requirement cannot be achieved.

Thanks,

Xi Jin.