locked
cross join in all power pivot relation RRS feed

  • Question

  • Hi,

    I am working on Office 365 Excel 2013.

    From last tuesday of this month (the 2015/02/19), all my new reports are false.

    Even with two simple tables joined by the same character key,  provide me the folowing wrong result :

    instead of having 1 as a total on the "A" key, I have 10, and you can see that keys of table two are multiplied.

    Is it a referenced bug ?

    Key(T1 &T2)      Total Table 1               Total table 2

    A                             1                                             10

    A             1                                            1

    B                                                            2

    C                                                            3

    D                                                           4


    Jerome

    Monday, February 23, 2015 9:05 AM

Answers

  • Of Course

    First Table

    %RUId %KAId %TransactionId Allocation TRID Clef Val
    US200_LT01 FR001_LT01 290929206 1 T290929206 A 1
    US200_LT01 FR001_LT01 290930249 1 T290930249 B 2
    US200_LT01 FR001_LT01 290931064 1 T290931064 C 3
    US200_LT01 FR001_LT01 291030235 1 T291030235 D 4
    US200_LT01 FR001_LT01 291030741 1 T291030741 E 5
    US200_LT01 FR001_LT01 291127764 1 T291127764 F 6
    US200_LT01 FR001_LT01 291128630 1 T291128630 G 7
    US200_LT01 FR001_LT01 291130005 1 T291130005 H 8
    US200_LT01 FR001_LT01 291030001 1 T291030001 I 9

    Second Table

    %RUId %KAId %TransactionId Margin TRID clef Val
    US200_LT01 FR001_LT01 290929206 231.41 T290929206 A 1
    US200_LT01 FR001_LT01 290930249 373.04 T290930249 B 2
    US200_LT01 FR001_LT01 290931064 3819.27 T290931064 C 3
    US200_LT01 FR001_LT01 291030235 14152.12 T291030235 D 4
    US200_LT01 FR001_LT01 291030741 7232.43 T291030741 E 5
    US200_LT01 FR001_LT01 291127764 30084.2 T291127764 F 6
    US200_LT01 FR001_LT01 291128630 15858 T291128630 G 7
    US200_LT01 FR001_LT01 291130005 400.92 T291130005 H 8
    US200_LT01 FR001_LT01 291030001 126696.89 T291030001 I 9

    At the begining, I tried to join this two table on the % Transaction ID. As I had the wrong result I created the TRID Field built with a concatenation to make the key to become characters. Finally I built a fake key with simple Letter.

    The result is a One to One relation as below wherre you can sumarize any information.

    Étiquettes de lignes Somme de Val
    A 1
    A 1
    B 2
    B 2
    C 3
    C 3
    D 4

    The one that I had was a cartesian product where each key of one table was related to all of the key of the second table

    Key table 1 - Key table 2  Somme de Val Somme de Val
    A 1 45
       A 1 1
       B 1 2
       C 1 3
       D 1 4
       E 1 5
       F 1 6
       G 1 7
       H 1 8
       I 1 9

    The solution, after the release, was to break the data model by removing the relation, one of the table and to start It again.

    Jerome

    Monday, March 2, 2015 9:38 AM

All replies

  • After a While,

    Waiting for the correction is impossible.

    The way to solve this probleme is to get the table out of the model, and do the same thing again.

    And the result is OK.

    

    Étiquettes de lignes Somme de Val
    A 1
    A 1
    B 2
    B 2
    C 3
    C 3


    Thursday, February 26, 2015 11:15 AM
  • can you please explain with input data and expected output...

    Regards,

    Amey

    Thursday, February 26, 2015 11:29 AM
  • Of Course

    First Table

    %RUId %KAId %TransactionId Allocation TRID Clef Val
    US200_LT01 FR001_LT01 290929206 1 T290929206 A 1
    US200_LT01 FR001_LT01 290930249 1 T290930249 B 2
    US200_LT01 FR001_LT01 290931064 1 T290931064 C 3
    US200_LT01 FR001_LT01 291030235 1 T291030235 D 4
    US200_LT01 FR001_LT01 291030741 1 T291030741 E 5
    US200_LT01 FR001_LT01 291127764 1 T291127764 F 6
    US200_LT01 FR001_LT01 291128630 1 T291128630 G 7
    US200_LT01 FR001_LT01 291130005 1 T291130005 H 8
    US200_LT01 FR001_LT01 291030001 1 T291030001 I 9

    Second Table

    %RUId %KAId %TransactionId Margin TRID clef Val
    US200_LT01 FR001_LT01 290929206 231.41 T290929206 A 1
    US200_LT01 FR001_LT01 290930249 373.04 T290930249 B 2
    US200_LT01 FR001_LT01 290931064 3819.27 T290931064 C 3
    US200_LT01 FR001_LT01 291030235 14152.12 T291030235 D 4
    US200_LT01 FR001_LT01 291030741 7232.43 T291030741 E 5
    US200_LT01 FR001_LT01 291127764 30084.2 T291127764 F 6
    US200_LT01 FR001_LT01 291128630 15858 T291128630 G 7
    US200_LT01 FR001_LT01 291130005 400.92 T291130005 H 8
    US200_LT01 FR001_LT01 291030001 126696.89 T291030001 I 9

    At the begining, I tried to join this two table on the % Transaction ID. As I had the wrong result I created the TRID Field built with a concatenation to make the key to become characters. Finally I built a fake key with simple Letter.

    The result is a One to One relation as below wherre you can sumarize any information.

    Étiquettes de lignes Somme de Val
    A 1
    A 1
    B 2
    B 2
    C 3
    C 3
    D 4

    The one that I had was a cartesian product where each key of one table was related to all of the key of the second table

    Key table 1 - Key table 2  Somme de Val Somme de Val
    A 1 45
       A 1 1
       B 1 2
       C 1 3
       D 1 4
       E 1 5
       F 1 6
       G 1 7
       H 1 8
       I 1 9

    The solution, after the release, was to break the data model by removing the relation, one of the table and to start It again.

    Jerome

    Monday, March 2, 2015 9:38 AM
  • Charbonnier,

    Is this still an issue?

    Thanks!


    Ed Price, Azure & Power BI Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Sunday, March 15, 2015 10:33 PM
  • Now It seams to be fixed, but it happens sometime on other extractions.

    Monday, March 23, 2015 2:06 PM