Grouping rows based on conditions

[newbiepbix]

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	Part1 only
A	Part2 only
A	NA
B	Part1 only
B	Part2 only
B	NA
C	Part1 only
C	NA
D	Part2 only
D	NA
E	NA

and the below table is what I am looking to get :

Title	Type
A	Both
B	Both
C	Part1 only
D	Part2 only
E	NA

Explanation : Both > Part1 AND Part2 > Part1 > Part 2 > NA.

(In case of Title B it comes as Both because it has two separate Part1 and Part2)

If anyone could please help me out it would be of great help.

Thanks!

[v-lili6-msft · Accepted solution]

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]

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

[amitchandak]

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