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

[tloup]

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. 

[Ankur04]

Hi,

could you please explain in details the exact requirement.

Thanks,

[tloup]

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]

Thanks for the reply from@VijayP and@Ankur04, 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.

[VijayP]

@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]

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