Forum Discussion

tloup's avatar
tloup
Frequent Visitor
2 years ago

Linking a custom table based on fact table with dimension table linked to the fact table

Hi

 

I have a custom table within my Power BI report that I want to link with a dimenstion table that's linked to my fact table. On the surface there are no obvious ways of joining the tables (custom & dimension) together using a standard drag and drop relationship creation. 

 

That said, making a relationship between the tables must be possible because the measures used for the custom table come from my fact table which is in turn linked to the department dimension table. 

 

Would be v grateful for any help at all!! 

 

Thanks. 

Need Help
Tips and Tricks
Tutorial Requests

5 Replies

  • VijayP's avatar
    VijayP
    Icon for Community Champion rankCommunity Champion
    2 years ago

    tloup 

    When you create a calculated table using DAX , it is not part of Data but it is part of Data model hence you cannot use it for the relation Data modelling. 
    To get this table as a table for RDB, you should create this table at Power Query Editor

    • tloup's avatar
      tloup
      Frequent Visitor
      2 years ago

      Is it possible to build a table in this way using measures that I've already coded in DAX ?

       

  • Ankur04's avatar
    Ankur04
    Icon for Resolver II rankResolver II
    2 years ago

    Hi,

     

    could you please explain in details the exact requirement.

     

    Thanks,

    • tloup's avatar
      tloup
      Frequent Visitor
      2 years ago

      Hi - so I have the below custom table which has been built using DAX code, and refers to measures already within the data model but when I use the dimension table to try and filter by market this table doesn't pick up the filter. I don't know how to build a table such as this using M code - is it even possible to refer to measures like I've done in DAX ?  

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Thanks for the reply fromVijayP andAnkur04, please allow me to provide another insight:

        Hi,tloup 

         

        Has the problem been solved? If it does, share your solution and accept it as a solution that will help other community members who have the same problem as you.

         

        Regarding the issue you raised, my solution is as follows:

         

        1.First of all, what needs to be shown to you is that in PowerQuery, you can't reference measures, calculated columns and calculated tables, if you need to reference table data, it is recommended to use PowerQuery to add custom columns, and then rebuild the data reference relationship.

        Add a column from examples - Power Query | Microsoft Learn

         

        2.Secondly, can you share the table relationship you have established, in the desktop, you can create the relationship between a normal table and a calculated table, but if it is a many-to-many relationship, it is very likely that the data will not be filtered.

        Create and manage relationships in Power BI Desktop - Power BI | Microsoft Learn

         

        If you have new discoveries, you are also welcome to share them with us.

         

        Best Regards,

        Leroy Lu

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.