Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Same values for sub dimension in a matrix

Hi All,

 

Please I need help with the issue below. The image below is the capture of our model

 

 

Now, we have the following measures (actual calculated from trial_balance fact table and budget calculated from Budget fact table):

actual = sum(trial_balance[Crédit Per])

budget = sum(Budget[BudgetAmount])

 

Now notice what is not working correctly in the image below (Hierarchy Level 1 (e.g LV, MV etc) Mapping dimension (Mapping column), Hierarchy Level 2 (e.g ALUCAM, SOCATRAL etc) Mapping dimension (Eneo requirement mapping column), Hierarchy Level 3 (e.g CIMAF, CIMENCAM etc) Sub Mapping dimension (Mapping1 column): As you can see below, the values highlighted are thesame which is incorrect. When I put Mapping1 column from Budget dimension as the third level hierarchy, the values for Budget are okay but not for Actual. When I put Mapping1 from Mapping des comptes, the values for Actual are okay but not for budget. When I put Mapping1 column from Sub mapping dimension, both Actual and Budget are thesame.

 

General Comment
Need Help
Show and Tell
Tips and Tricks

3 Replies

  • v-gizhi-msft's avatar
    v-gizhi-msft
    Community Support
    6 years ago

    Hi,

     

    According to your description and screenshot, i am confused with the relationships among these tables.

    Please give me more details about which column is used as one and which is used as many between each two tables' one-to-many relationships?

    And i also need to know the three columns in matrix come from what tables?

     

    Best Regards,

    Giotto Zhi

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi v-gizhi-msft ,

       

      Please find below your request.

       

      Please give me more details about which column is used as one and which is used as many between each two tables' one-to-many relationships?

       

      1. Between Mapping and SubMapping

      * The Mapping column (1) in Mapping table and the Mapping column (*) in Sub Mapping table

       

      2. Between Mapping and Mapping des Comptes

      * The Mapping column (1) in Mapping table and the Mapping column (*) in Mapping des comptes table

       

      3. Between Mapping and Budget

      * The Mapping column (1) in Mapping table and the Mapping column (*) in Budget table

       

      4. Between Mapping des comptes and trial_balance

      * The SYSCOHADA Account column (1) in Mapping des comptes table and the Numero/account number column (*) in trial_balance table

       

      5. Between DimDate and Budget

      * The Date column (1) in DimDate table and the Date column (*) in Budget table

       

      6. Between DimDate and trial_balance


      * The Date column (1) in DimDate table and the Date column (*) in trial balance table

       

       

      And i also need to know the three columns in matrix come from what tables?

       

      The three columns are as follows from first to last

       

      1. Mapping [Eneo requirement mapping]

      2. Mapping [Mapping]

      3. Sub Mapping [Mapping 1]

       

      • v-gizhi-msft's avatar
        v-gizhi-msft
        Community Support
        6 years ago

        Hi,

         

        Thanks for your detailed explanation, and i found the problem in these relationships:

        I create a silimar table model with you and for your issue, it is because the 'Sub Mapping'[Mapping 1] can not directly filter the 'Budget'[BudgetAmount], and they are both filtered by Table 'Mapping'.

        So, i try to rebuild your relationships:

        1)Create a table to connect Table 'Budget' and 'Mapping':

         

        Level 2 = DISTINCT(SELECTCOLUMNS('Sub Mapping',"Mapping",'Sub Mapping'[Mapping],"Mapping 1",'Sub Mapping'[Mapping 1]))

         

        It is easy to create the relationship between Table 'Mapping' and 'Level 2':

        But when i try to create the relationship between Table 'Budget' and 'Level 2', it has a problem that i need more data because the original table used [Mapping] as key column and this time i need [Mapping 1] as key column.

        And the Table 'Trial_balance' is the same problem.

        So, you need to rebuild your whole table model.

        Here is my test pbix file:

        pbix 

        Hope this helps.

         

        Best Regards,

        Giotto Zhi