Forum Discussion

apollo89's avatar
apollo89
Helper II
8 years ago
Solved

left outer join using dax, Multiple to Multiple

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    0...
  • v-xjiin-msft's avatar
    v-xjiin-msft
    8 years ago

    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.

v-xjiin-msft's avatar
v-xjiin-msft
Solution Sage
8 years ago

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's avatar
apollo89
Helper II
8 years ago

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's avatar
    v-xjiin-msft
    Solution Sage
    8 years ago

    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.