Return a Value of a column depends on the values from another table

[Anonymous]

I am trying to add another column that returns a value (either "Both", "with Design only" or "with As-Built only") from another source table that may contain more than 1 value.

Work Order	Estimate Type
C1509036072	Design
P1510000938	Design
C1511010088	Design
C1510004452	Design
C1511029159	Design
C1509036829	Design
C1511013021	Design
C1509036072	As-Built
C1510039895	As-Built
C1511010088	As-Built
C1510032088	As-Built
C1511029159	As-Built
C1511014036	As-Built
C1511013021	As-Built

the result should be:

Work Order	Result
C1509036072	Both
P1510000938	Design Only
C1510039895	As-Built Only
C1511010088	Both
C1510004452	Design Only
C1510032088	As-Built Only
C1511029159	Both
C1509036829	Design Only
C1511014036	As-Built Only
C1511013021	Both

[harshnathani]

Hi @Anonymous ,

Incase you need a measure

One or Both =
VAR _countdesign =
    COUNTX (
        FILTER (
            'Table',
            [Work Order]
                = MAX ( [Work Order] )
                && [Estimate Type] = "Design"
        ),
        [Work Order]
    ) + 0
VAR _countasbuilt =
    COUNTX (
        FILTER (
            'Table',
            [Work Order]
                = MAX ( [Work Order] )
                && [Estimate Type] = "As-Built"
        ),
        [Work Order]
    ) + 0
VAR _sum = _countdesign + _countasbuilt
RETURN
    SWITCH (
        TRUE (),
        _sum > 1, "Both",
        _countdesign >= 1
            && _countasbuilt = 0, "Design Only",
        "As-Built Only"
    )

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Anonymous]

Thank you for the formula. I forgot to mention that in the table there will be multiple rows for every Work Order that consist of either design or as-built value because there is another field involve, for example:

Work Order	Estimate Type	CU
C1509036072	As-Built	LHW2-SDE
C1509036072	As-Built	SAS2-UA-XDE
C1509036072	As-Built	GUY2-MRK
C1509036072	As-Built	GUY2-TCL
C1509036072	As-Built	WIR2-G38
C1509036072	As-Built	CBL2-AT30
C1509036072	As-Built	CON2-W302

and the same goes for other Work orders. I don't know if the formula you've given is applicable for this one.

Thanks a lot!

[harshnathani]

Hi @Anonymous ,

The formula should work.

Else can you share sample data and the expected output.

Regards,

Harsh Nathani

[Anonymous]

Here's the sample data:

Work Order	Estimate Type	CU
C1711005413	DESIGN	GEN1-ET-RSC-MV
C1711005413	DESIGN	CON1-C-ABR
C1711005413	DESIGN	CON2-W3030
C1711005413	DESIGN	CON2-W3030A
C1711005413	DESIGN	GEN1-ELECTAPE-HV-B-V
C1711005413	ASBUILT	CBL2-AT30
C1711005413	ASBUILT	CON2-W302
C1711005413	ASBUILT	FST1-B16O150S
C1711005413	ASBUILT	GEN1-ELECTAPE-LV-B
C1711005413	ASBUILT	GRD2-INS-C1
C1711005413	ASBUILT	GUY2-ANC-1-18-OVL
C1507030290	ASBUILT	WIR2-T30-1INS
C1507030290	ASBUILT	POL2-IC13505
C1507030290	ASBUILT	LHW2-SSTA
C1507030290	ASBUILT	INS3-B-PST-PP170E
C1507030290	ASBUILT	SAS2-UA-ANG
C1508002877	DESIGN	CON2-W3010
C1508002877	DESIGN	CON2-W302A
C1508002877	DESIGN	INS3-L-PDE-33B
C1508002877	DESIGN	SAS2-UA-XDE
C1508002877	DESIGN	TAG2-PLT-A
C1508002877	DESIGN	CND2-BA30

and this should be the result:

Work Order	Result
C1711005413	Both
C1507030290	ASBUILT Only
C1508002877	DESIGN Only

[harshnathani]

HI @Anonymous ,

One or Both = 
var _countdesign = countx(filter('Table', [Work Order] = MAX([Work Order]) && [Estimate Type] = "DESIGN"),[Work Order])+0
var _countasbuilt = countx(filter('Table', [Work Order] = MAX([Work Order]) && [Estimate Type] = "ASBUILT"),[Work Order])+0
var _sum = _countdesign + _countasbuilt
return
switch ( true(),
_countdesign >=1 && _countasbuilt = 0 ,  "Design Only" ,

_countasbuilt >=1 && _countdesign = 0 ,  "ASBULIT Only" ,
"BOTH"
)

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[amitchandak]

@Anonymous , Try as a new column

new column  = 
var _cntd = countx(filter(Table, [Work Order] =earlier([Work Order]) && [Estimate Type] = "Design"),[Work Order])+0
var _cntb = countx(filter(Table, [Work Order] =earlier([Work Order]) && [Estimate Type] = "As-Built"),[Work Order])+0
return
switch ( true(),
_cntd >=1 && _cntb>= 1, "Both",
_cntd >=1 && _cntb =0 ,  "Design Only" ,
"As-Built Only"
)

[Anonymous]

Thank you for the formula. I forgot to mention that in the table there will be multiple rows for every Work Order that consist of either design or as-built value because there is another field involve, for example:

Work Order	Estimate Type	CU
C1509036072	As-Built	LHW2-SDE
C1509036072	As-Built	SAS2-UA-XDE
C1509036072	As-Built	GUY2-MRK
C1509036072	As-Built	GUY2-TCL
C1509036072	As-Built	WIR2-G38
C1509036072	As-Built	CBL2-AT30
C1509036072	As-Built	CON2-W302

and the same goes for other Work orders. I don't know if the formula you've given is applicable for this one.

Thanks a lot!

[amitchandak]

@Anonymous , Try that and let me know issues.

[Greg_Deckler]

@Anonymous - I'm thinking something like:

Estimate Type =
  VAR __Table = SUMMARIZE(RELATEDTABLE('OtherTable'),[Result])
RETURN
  IF(COUNTROWS(__Table)>1,"Both",MAXX(__Table,[Result]))