Forum Discussion

newbiepbix's avatar
newbiepbix
Microsoft Employee
6 years ago
Solved

Grouping rows based on conditions

Hi Everyone, I am new to PBI and needed some help.I have a scenario where i need the rows to be merged / grouped: The below table is what i have in my data :   Title Type A Both A P...
General Comment
Need Help
Tutorial Requests
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    hi  newbiepbix 

    Just use this logic to create a new table

    New Table = 
    SUMMARIZE (
        'Table',
        'Table'[Title],
        "Type", IF (
            "Both" IN VALUES ( 'Table'[Type] ),
            "Both",
            IF (
                "Part1 only" IN VALUES ( 'Table'[Type] )
                    && "Part2 only" IN VALUES ( 'Table'[Type] ),
                "Both",
                IF (
                    "Part1 only" IN VALUES ( 'Table'[Type] ),
                    "Part1 only",
                    IF ( "Part2 only" IN VALUES ( 'Table'[Type] ), "Part2 only", "NA" )
                )
            )
        )
    )

    Result:

     

    and here is sample pbix file, please try it.

     

    Regards,

    Lin

v-lili6-msft's avatar
v-lili6-msft
Community Support
6 years ago

hi  newbiepbix 

Just use this logic to create a new table

New Table = 
    SUMMARIZE (
        'Table',
        'Table'[Title],
        "Type", IF (
            "Both" IN VALUES ( 'Table'[Type] ),
            "Both",
            IF (
                "Part1 only" IN VALUES ( 'Table'[Type] )
                    && "Part2 only" IN VALUES ( 'Table'[Type] ),
                "Both",
                IF (
                    "Part1 only" IN VALUES ( 'Table'[Type] ),
                    "Part1 only",
                    IF ( "Part2 only" IN VALUES ( 'Table'[Type] ), "Part2 only", "NA" )
                )
            )
        )
    )

Result:

 

and here is sample pbix file, please try it.

 

Regards,

Lin

newbiepbix's avatar
newbiepbix
Microsoft Employee
6 years ago

Thank you so much!!!! This worked like a charm.