Item with multiple values

[Chipsahoy1]

Hi All,

Thanks in advance for your help. I'm a Power BI newbie and need some help.

In the table below a PO # can have multiple values listed under the Primary/Revision column. What I need help with is if a PO # has primary, revision, and TBD listed (PO # 1), or primary and TBD listed (PO # 3) I need all the rows under the Type column to list primary for those particular PO #s.  If a PO # has revision and TBD (PO # 2), I need revision listed for all rows under the Type column for that particular PO #.  Is there a DAX can do this?

PO #	PO Line #	Primary/Revision	Type
1	1	TBD	Primary
1	2	Primary	Primary
1	3	Revision	Primary
2	1	TBD	Revision
2	2	Revision	Revision
3	1	TBD	Primary
3	2	Primary	Primary
3	3	TBD	Primary
3	4	TBD	Primary
3	5	TBD	Primary

[Jihwan_Kim]

Hi, @Chipsahoy1 

Please correct me if I wrongly understood your question.

Please check the below picture and the formula for creating a new column.

Type CC =

VAR currentpo = 'Table'[PO #]

VAR potableconditioncolumn =

SUMMARIZE (

FILTER ( 'Table', 'Table'[PO #] = currentpo ),

'Table'[Primary/Revision]

)

RETURN

SWITCH ( TRUE (), "Primary" IN potableconditioncolumn, "Primary", "Revision" )

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Chipsahoy1]

Thanks for your quick response @Jihwan_Kim.I forgot to mention an important aspect in my post. If I have a PO # that only has TBD listed under the Primary/Revision column, I need it to have TBD listed for all the rows under the type column for that particular PO #. Right now, your DAX code is listing Revision for all the PO #s that only have TBD listed.  My apologies. 

[Jihwan_Kim · Accepted solution]

Hi, @Chipsahoy1 

Thank you for your feedback.

I tried to add few more columns into your sample to check.

Please check the below picture and the sample pbix file's link, whether it suits your case.

Type CC =

VAR currentpo = 'Table'[PO #]

VAR potableconditioncolumn =

SUMMARIZE (

FILTER ( 'Table', 'Table'[PO #] = currentpo ),

'Table'[Primary/Revision]

)

RETURN

SWITCH (

TRUE (),

"Primary" IN potableconditioncolumn, "Primary",

"TBD"

IN potableconditioncolumn

&& COUNTROWS ( potableconditioncolumn ) = 1, "TBD",

"Revision"

)

https://www.dropbox.com/s/0829amadf9mo4fy/chipsahoy.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Chipsahoy1]

@Jihwan_Kim thank you for your help!  Your formula worked like a charm.