Req help with dim and measures

[Anonymous]

Hi, I have 2 fact tables sales invoice and sales history. I have customer card dimension which I could join with fact sales invoice and couldn't join with sales history because of data at various levels. Below is the model
Sales invoice having join with sales header and item card, sales header joined to customer card and date.
Sales history joined with item card, date and to be joined with customer card as well, but the join not happen with customer card.
The measure is sales amount = sales invoice amount + sales history amount . But if I choose customer name from customer card dim, measure is not working properly as customer card not actively joined to sales history. If I get the userelationship function. I am seeing sales, but sales is same for each customer, it's not slicing by customer name.
Help could be appreciated with the solution. Thanks.
Shekar

[dax · Accepted solution]

Hi shekarsuri, 

If possible,  could you please inform me more detailed information (such as your sample data and expected output)? Then I will help you more correctly. Or  you could use simple sample data to replace your real and complex sample and upload your pbix file.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

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

[Anonymous]

Hi Zoe

Hi Dax,

here is my sample pbix and relation ship diagram.

https://drive.google.com/file/d/15wnXMc86BSFNwbk3fLh20ZR1eJLKzYiZ/view?usp=sharing

table relationship image

https://drive.google.com/file/d/1PqiN_cGGKPzI5aFkoQiEGON5wVouNxAN/view?usp=sharing

Let me explain about the requirement

We have 2 companies setup: company A and company B, would like to reporting on 2companies to gether

now we are reporting on each company seperately. as mentioned in the relationship diagram, all the tables are same structure in both companies.

we brought one more file which have sales history

what i have done:

1. added company name column for each table in 2 companies to refer the data belongs to which company

2. appended company A and Company b all tables except F3, as F3 contains all the company information

3. join the tables as mentioned in the diagram ( have 3 sets of different tables with the same structure)

4. Brought the Sales history file (Table 3 in diagram) and establish the relations mentioned in the diagram

Issue: while joining F3 with D3  and couldn't use the columns in the report. required advise how to join or how to use this table in the report without join

Idea: can i use the different copies of Dims for F3 and can we merge and use.

Because I have to add the measures summation for the reports.

Thanks and let me know if you have any questions

[dax]

Hi shekarsuri,

Your data sample is a little complex, and  it seems that many tables have the same fields. I think you need to join or combine it if you want to show them in the same visual. In addition, you said that you want to use "different copies of Dims for F3 and can we merge and use", if possible, could you please inform me your expected output, I think you might could use M code to summarize table.

By the way, if possible, could you explain the steps in "what I have done"? I didn't find corresponding table or column name in your sample.

Best Regards,
Zoe Zhi

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

[Anonymous]

Table Names and structure:

2 companies - AGT , AGU. Tables are same for both companies

postedsalesinvoicelines_agt --> Postedsalesinvoiceheader_agt
postedsalesinvoicelines_agt --> Item_Card_agt
Postedsalesinvoiceheader_agt --> Customer_Card_AGT
Postedsalesinvoiceheader_agt --> Fiscal_Date

postedsalesCreditlines_agt --> Postedsalescreditmemo_agt
postedsalesCreditlines_agt --> Item_Card_agt
Postedsalescreditmemo_agt --> Customer_Card_AGT
Postedsalescreditmemo_agt --> Fiscal_Date

Salesordersalesline_agt --> salesorder_agt
Salesordersalesline_agt --> Item_Card_agt
salesorder_agt --> Customer_Card_AGT
salesorder_agt --> Fiscal_Date

postedsalesCreditlines_agu --> Postedsalescreditmemo_agu
postedsalesCreditlines_agu --> Item_Card_agu
Postedsalescreditmemo_agu --> Customer_Card_AGu
Postedsalescreditmemo_agu --> Fiscal_Date

postedsalesinvoicelines_agu --> Postedsalesinvoiceheader_agu
postedsalesinvoicelines_agu --> Item_Card_agu
Postedsalesinvoiceheader_agu --> Customer_Card_AGu
Postedsalesinvoiceheader_agu --> Fiscal_Date

Salesordersalesline_agu --> salesorder_agu
Salesordersalesline_agu --> Item_Card_agu
salesorder_agu --> Customer_Card_AGu
salesorder_agt --> Fiscal_Date

combined these 2 companies (Tables) using append queries
----------------------------------------------
postedsalesCreditlines_agt_agu --> Postedsalescreditmemo_agt_agu
postedsalesCreditlines_agt_agu --> Item_Card_agt_agu
Postedsalescreditmemo_agt_agu --> Customer_Card_agt_AGu
Postedsalescreditmemo_agt_agu --> Fiscal_Date

postedsalesinvoicelines_agt_agu --> Postedsalesinvoiceheader_agt_agu
postedsalesinvoicelines_agt_agu --> Item_Card_agt_agu
Postedsalesinvoiceheader_agt_agu --> Customer_Card_agt_AGu
Postedsalesinvoiceheader_agt_agu --> Fiscal_Date

Salesordersalesline_agt_agu --> salesorder_agt_agu
Salesordersalesline_agt_agu --> Item_Card_agt_agu
salesorder_agt_agu --> Customer_Card_agt_AGu
salesorder_agt_agu --> Fiscal_Date

new table with history and to be joined as
------------------------
scoopsoft_sales --> item_Card_AGT_AGU
scoopsoft_sales --> Customer_Card_agt_AGu
scoopsoft_sales --> Fiscal_Date

expected output: scoopsoft_sales having data before 2019 june

remaining tables have data from June 2019.

We would have all these data in single report.

Thanks and let me know if any questions

[Anonymous]

Could you please send your email id, i will share my Pbix. thanks

[Anonymous]

sure, I will send the details soon.

Thanks