Forum Discussion

Chipsahoy1's avatar
Chipsahoy1
Helper I
5 years ago
Solved

Item with multiple values

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 hel...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    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"
    )
     
     

    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

Jihwan_Kim's avatar
Jihwan_Kim
Super User
5 years ago

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's avatar
    Chipsahoy1
    Helper I
    5 years ago

    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's avatar
      Jihwan_Kim
      Super User
      5 years ago

      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"
      )
       
       

      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's avatar
        Chipsahoy1
        Helper I
        5 years ago

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