cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Microsoft Employee

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

Thanks!

1 ACCEPTED SOLUTION
Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Microsoft Employee

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

Super User

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