Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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!
Solved! Go to 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
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
Thank you so much!!!! This worked like a charm.
@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"
)
User | Count |
---|---|
128 | |
72 | |
70 | |
58 | |
53 |
User | Count |
---|---|
192 | |
96 | |
67 | |
64 | |
54 |