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 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/RevisionType
11TBDPrimary 
12PrimaryPrimary 
13Revision Primary 
21TBDRevision 
22RevisionRevision 
31TBDPrimary 
32PrimaryPrimary
33TBDPrimary
34TBDPrimary
35TBDPrimary
  • 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

4 Replies

  • 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