topic Re: Item with multiple values in DAX Commands and Tips

Item with multiple values

Chipsahoy1 — Fri, 07 May 2021 04:58:17 GMT

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

Re: Item with multiple values

Jihwan_Kim — Fri, 07 May 2021 06:27:07 GMT

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

Re: Item with multiple values

Chipsahoy1 — Fri, 07 May 2021 18:49:52 GMT

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.

Re: Item with multiple values

Jihwan_Kim — Sat, 08 May 2021 03:45:21 GMT

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

Re: Item with multiple values

Chipsahoy1 — Wed, 12 May 2021 23:40:58 GMT

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