Measure based on different relationships (Differente fields in the same table)

[rodrigorfsilva]

Hi all,

I'm looking for a solution since last week, but couldn't find it yet. I have a fact table (TableSales) and a project table (TableProjects) as follows:

TableSales

Customer;Product;SalesDate;NetSales;Project_1;Project_2;Project_3
Energy Co;Smart Meter;07/15/19;750.000;Com. RF;;
Energy Co;Smart Meter;10/20/19;1.350.000;Com. RF;;
Energy Co;Smart Meter 2.0;06/10/19;1.350.000;Com. 4G;Anti Theft;
Energy Co;Mechanical Meter;10/30/19;1.500.000;Anti Theft;;
State Co;Smart Meter;05/15/19;900.000;Com. RF;;
State Co;Smart Meter;01/10/20;900.000;Com. RF;;
State Co;Smart Meter 2.0;09/10/19;1.050.000;Com. 4G;Anti Theft;
State Co;Smart Meter 2.0;01/02/20;1.350.000;Com. 4G;Anti Theft;
State Co;Smart Meter 3.0;08/20/19;1.350.000;Com. 4G;Cloud DG;Anti Theft

TableProjects

ProjectID;StartDate;EndDate;Investment
;;;
Com. RF;04/01/19;09/30/19;150.000
Com. 4G;07/01/19;12/31/19;250.000
Cloud DG;07/01/19;03/31/20;350.000
Anti Theft;04/01/19;06/30/19;125.000

I need to show the NetSales depending on each project. What happens is that I may need one, two or three projects at once. I need to show in my report the earliest StartDate; latest EndDate and the the sum of Investment.

I don't know how to make a relationship between my two tables, since I need to use all of three project dependency columns form TableSales and connect them with ProjectID in projects table.

Thank you all for your support.

Regards,

Rodrigo

[v-chuncz-msft · Accepted solution]

@rodrigorfsilva,

You may try Unpivot Columns in Query Editor or USERELATIONSHIP in DAX.

[Greg_Deckler]

Nope, not clear. Sample data would help along with expected results. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[rodrigorfsilva]

Thank you Greg for your advice. This is the first time I'm sending a question here. Sorry for didn't make it clear ate first. Hope it is better now.

Rgds, Rodrigo