get sum of values considering one value in case of duplicate records

[Anonymous]

Hi All,

I have my factless fact tablle like below, which explains that I have three objects Tags,engg_doc and Issues each of them can be related one to many (eg. a tag can have many documents related, issues can have many Docs related to one). I need to sum the earned value of the documents that are related to issues but I should consider only one value in case of duplicates. eg. from the below tables the highlighted documents in green color and their earned values shall be considered in the summation while the red ones shall not be considered as they are duplicates. 

Fact table:

Tag_Name	Engg_Doc_Name	Issue_Name
T-101	Doc-101
T-101	Doc-102
T-101	Doc-103
T-101	Doc-104
T-101	Doc-105
T-101		I-100
T-101		I-101
T-101		I-102
T-101		I-103
T-101		I-104
T-102	Doc-104
T-102	Doc-105
T-102	Doc-106
T-102	Doc-107
T-103	Doc-101
T-103	Doc-102
T-103	Doc-103
T-103	Doc-104
T-103	Doc-105
T-103	Doc-106
T-103	Doc-107
T-103	Doc-108
T-103	Doc-109
	Doc-104	I-105
	Doc-105	I-105
	Doc-106	I-105
	Doc-107	I-105
	Doc-108	I-105
	Doc-109	I-105
	Doc-110	I-105
	Doc-111	I-105
	Doc-109	I-106
	Doc-110	I-106
	Doc-111	I-106
	Doc-104	I-106
	Doc-105	I-106
	Doc-106	I-106
	Doc-112	I-107
	Doc-113	I-107
	Doc-114	I-107
	Doc-115	I-107
	Doc-116	I-107

my document dimension table looks like this.

Name	Type	Revision	Sub FunctionDiscipline	DocType	Discipline	Earned Value	Total Mnhrs
Doc-101	Engineering Document	A	Mechanical	P&ID	Mechanical	80	100
Doc-102	Engineering Document	B	Electrical	P&ID	Electrical	27	75
Doc-103	Engineering Document	B	Instrumentation	P&ID	Instrumentation	57	65
Doc-104	Engineering Document	B	Piping	P&ID	Piping	22	81
Doc-105	Engineering Document	B	Piping	P&ID	Piping	79	91
Doc-106	Engineering Document	B	Piping	P&ID	Piping	99	101
Doc-107	Engineering Document	B	Piping	P&ID	Piping	123	231
Doc-108	Engineering Document	B	Piping	P&ID	Piping	143	156
Doc-109	Engineering Document	B	Piping	P&ID	Piping	112	112
Doc-110	Engineering Document	B	Electrical	DS	Electrical	27	90
Doc-111	Engineering Document	B	Instrumentation	DS	Instrumentation	22	89
Doc-112	Engineering Document	B	Instrumentation	DS	Instrumentation	22	89
Doc-113	Engineering Document	B	Instrumentation	DS	Instrumentation	22	89
Doc-114	Engineering Document	B	Instrumentation	DS	Instrumentation	22	89
Doc-115	Engineering Document	B	Instrumentation	DS	Instrumentation	22	89
Doc-116	Engineering Document	B	Instrumentation	DS	Instrumentation	22	89

I have tried something like below and its not excluding the duplicates can anyone help me with this?

Distinct sum Measure = CALCULATE(SUM('Fact'[deficit lookup]),'Fact'[Issue_Name]<> BLANK(),'Fact'[Idocs_Engg_Doc_Name]<>BLANK(),DISTINCT('Fact'[Idocs_Engg_Doc_Name]))

regards,

CK

[Anonymous · Accepted solution]

I was able to achieve it using the below snippet

Deficit EV = CALCULATE(SUMX(DISTINCT('Fact'[Idocs_Engg_Doc_Name]),FIRSTNONBLANK('Fact'[deficit lookup],0)),'Fact'[Issue_Name]<>BLANK(),'Fact'[Idocs_Engg_Doc_Name]<>BLANK())

regards,

CK