Combining Two fact table expressions in matrix chart - No direct relationship in one fact table

[prakash11440278]

HI All,

I have a requirement in matrix chart to pull the data from two different fact tables and two different dimension tables. Where i have a direct relationship between one dimension table name called Category & other fact table name Fact table 1 but there is no direct relationship between Category & Fact table2. Is it possible to display the category wise Sales data from both fact tables? The relation ship between Category & Fact table 1 is joined by category id where the same column is existed in both the tables, but in Fact table 2 we dont have any category id's present becuase of that we cant join directly. The Category column will dynamically change based on the backend data, we cannot hard code and provide the values accordingly by using switch condition. 

From the below screenshot i am not able to see any data in A & E columns in Expected output table becuase we dont have any relationship existed in Category & Fact table 2 tables. The requirement i can acheive by adding the static category values in new table and pass the respective measures using switch condition but in my req. from B to D the category values will change dynamically. How to acheive the expected result. Please help here.

[v-angzheng-msft]

Hi, @prakash11440278 

This is not very clear to me. How can the data from fact table 2 be obtained in the expected output? What is the purpose of a custom table? How does the ID field of the custom table and fact table 2 map to the category ID of the category table? What is the expected complete output result?
To save time in creating a PBIX, could you please share the sample files of the dummy data so that we can know more details.

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[prakash11440278]

Hi @v-angzheng-msft ,

Thnaks for your reply.

1)How can the data from fact table 2 be obtained in the expected output?

Yes we need fact table2 data ( A & E) columns to be included in our desired output. If you see there is no direct relatinship between Fact table2 & Category tables. Is it possible to add like a measure below:

SWITCH(

SELECTEDVALUE(table[category]),

"A",SUM(SALES),

"B",SUM(PROFIT),

[MARGIN])

Lets assume margin in else condition is a dynamic calculation will give the result of the B,C & D columns. Even i tried the above expression it is not giving the result for the A & B columns, where i am able to see the data for B,C & D columns.

2)  What is the purpose of a custom table?
As of now pls ignore the customer table and the cust column X,Y & Z are showing in y axis for showing with respect to customer data.

3) How does the ID field of the custom table and fact table 2 map to the category ID of the category table?

Please ignore the customer relationship.

4)  What is the expected complete output result?

As i showned in the expected output table above, for the columns A& E i want the data to be integrated based on the category wise. In the table A&E are static text it wont change & where as B,C & D are the dynamic columns coming from fact table 1 where the relationship already existed using common column category id b/w both the tables.