Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The 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.

Reply
Yamabushi
Helper I
Helper I

Finding count of possible combinations

Hi,

 

I have a table that looks something like this:

PersonCriteria 1Criteria 2Criteria 3
A101
B110
C010
D010
E110
F001

 

I need to create a transformation that will count the number of times a person has a combination of different criteria:

Category 1Category 2Count
Criteria 1Criteria 13
Criteria 1Criteria 22
Criteria 1Criteria 31
Criteria 2Criteria 12
Criteria 2Criteria 24
Criteria 2Criteria 30
Criteria 3Criteria 11
Criteria 3Criteria 20
Criteria 3Criteria 32

 

Any advice would be helpful.

 

Thank you!

1 ACCEPTED SOLUTION
slorin
Super User
Super User

Hi @Yamabushi 

 

let
Source = YourSource,
Unpivot = Table.UnpivotOtherColumns(Source, {"Person"}, "Criteria1", "Value"),
Join = Table.NestedJoin(Unpivot, {"Person"}, Unpivot, {"Person"}, "Unpivot", JoinKind.LeftOuter),
Expand = Table.ExpandTableColumn(Join, "Unpivot", {"Criteria1", "Value"}, {"Criteria2", "Value2"}),
Product = Table.AddColumn(Expand, "Product", each [Value] * [Value2], type number),
Group = Table.Group(Product, {"Criteria1", "Criteria2"}, {{"Count", each List.Sum([Product]), Int64.Type}})
in
Group

Stéphane 

View solution in original post

4 REPLIES 4
slorin
Super User
Super User

Hi @Yamabushi 

 

let
Source = YourSource,
Unpivot = Table.UnpivotOtherColumns(Source, {"Person"}, "Criteria1", "Value"),
Join = Table.NestedJoin(Unpivot, {"Person"}, Unpivot, {"Person"}, "Unpivot", JoinKind.LeftOuter),
Expand = Table.ExpandTableColumn(Join, "Unpivot", {"Criteria1", "Value"}, {"Criteria2", "Value2"}),
Product = Table.AddColumn(Expand, "Product", each [Value] * [Value2], type number),
Group = Table.Group(Product, {"Criteria1", "Criteria2"}, {{"Count", each List.Sum([Product]), Int64.Type}})
in
Group

Stéphane 

Yes! Thank you 🙂

Yamabushi
Helper I
Helper I

Hi,

 

Thanks for your answer, but sadly this is not the solution I'm looking for.

I am looking for a way to transform the first table into the structure seen in the second table. It has all the combinations of Criteria listed in 2 separate columns while the third column shows how many times a person was positive in both criteria of a separate row.

ChielFaber
Responsive Resident
Responsive Resident

If I'm interpreting your question the right way you can just use the group by function in the UI. 

let
Source = PUTYOURSOURCEHERE, 
#"Grouped Rows" = Table.Group(Source, {"Criteria 1", "Criteria 2", "Criteria 3"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
#"Grouped Rows"

ChielFaber_1-1728992604801.png

 



Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors