Forum Discussion

newbiepbix's avatar
newbiepbix
Icon for Microsoft Employee rankMicrosoft 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

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

newbiepbix , create a new measure like this and try

 

new measure =Switch(
countx(filter(table,table[Type]="Both"),table[Title])>=1,"Both",
countx(filter(table,table[Type]="Part1 AND Part2"),table[Title])>=1,"Part1 AND Part2",
countx(filter(table,table[Type]="Part1"),table[Title])>=1,"Part1",
countx(filter(table,table[Type]="Part 2"),table[Title])>=1,"Part 2",
"NA"
)